Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm having trouble in calculate yesterday's sales in set analysis.
The today's sales formula works:
Sum({<DateNum={"$(=Max(DateNum))"}>} Value)
But yesterday's sales formula does not:
Sum({<DateNum={"$(=Max(DateNum-1))"}>} Value)
What's wrong?
What is this? $(vSetAnalysisIgnoreAll)
It's variable that clears ALL FIELDS chosen by user in the model
I tried using variables as well.
After all this, I tries the simplest - just using a static value such as in my examples: Date={"02/11/2016"}.
Once this works, of course I'll change that to dynamic formula which is:
Sum({<$(vSetAnalysisExceptTimes), DateNum={"$(=Max(DateNum-1))"}>} Value)
By the way, the same formula but for Max(DateNum) works!!! Here it is:
Sum({<$(vSetAnalysisExceptTimes), DateNum={"$(=Max(DateNum))"}>} Value)
All I need id the same thing but for 1 day earlier but nothing seems to work
Would you be able to show the expression behind it?
='['&concat(distinct{$<$Field={'*'}>}$Field,']='&',[')&']='
By the way, if I make a selection in any other Calendar field other than "Date" (for example, Year or Month or Quater), the formula works.
ONLY IF I MAKE A SELECTION OF DATE IN "DATE" FIELD, the formula doesn't work with following exception:
If I make selection of 2 following dates, as 1/11/2016 and 2/11/2016 it works.
But if I choose only 1 date - 2/11/2016 it shows 0 for previous date (1/11/2016).
Is there a reason you use Concat($Field) instead of 1 to ignore all selections?
Just wanted to check if it works.
I don't want to clear all fields and I don't want to use "1" in set analysis.
I want the formula to be dynamic.
THE ONLY THING THAT CORRUPTS MY FORMULA IS IF THE USER MAKES SELECTIONS IN "DATE" FIELD!
So this won't work if you make selection in Date field?
Sum({<DateNum={"$(=Max(DateNum)-1)"}, Date>} Value)
what is ", Date" ?