I am building a dash board with Date as filter, in query browser I connect to universe and build query selecting dimension, measure with Date as filter (Equal to “Prompt”), Prompt I have deselected all options. I bound a Cell in excel as input, another cell for date, measure for output.
I used excel formula Today()-1 as input filed.
When use formula its not fetching any data but if I hard code date its fetching data, I understand that formula/prompt isn’t working.
If you display just the date field, what do you see?
As a note, Excel functions such as Today() and Now() don’t always work as expected in Xclesius. Most particularly, they don’t update when the SWF file runs. They will just continue to use the value calculated when the SWF was generated.
The simplest workaround is to set up the formula to recalculate when another cell changes. For instance, if you have a list of default prompt values that populates cell A2, you can make your formula:
=IF(A2="",TODAY() - 1,TODAY() - 1)
Obviously, this gets tricky if your connection that loads the prompt values needs that date to return the correct list. There are a couple other options. You can use a play component to automatically change a cell as the dashboard is opening. This can serve to both update the date cell and allow you to pause when the connections initially fire off. You could also create a connection that just pulls back the current date, rather than calculating it at the Excel level.