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?
OK.
I found the really stupid mistake I've made:
In the filter pane in the Date field was written: =Date(Date)
(attached).
and that's why all selections in that field corrupted my correct formula which is:
Sum({<$(vSetAnalysisExceptTimes), DateNum={"$(=Max(DateNum-1))"}>} Value)
THANKS EVERYONE FOR ANSWERING, GIVING INTERESTING IDEAS AND BEING HELPFUL!!!
Try this:
Sum({<DateNum={"$(=Max(DateNum)-1)"}>} Value)
I tried that.
Unfortunately, that doesn't work either...
try
Sum({<DateNum={"$(=num(Max(DateNum)-1))"}>} Value)
What's weird is that if the date is chosen, the value shows but if its not, than it shows 0.
Example 1:
If I put a formula like this:
Sum({<$(vSetAnalysisExceptTimes), Date={"02/11/2016"}>} Value) and I have chosen these dates: 01/11/2016,
02/11/2016, 03/11/2016 than I have a correct value.
But if I choose only these dates: 01/11/2016 and 03/11/2016 than I have 0 as value which is wrong.
**Variable vSetAnalysisExceptTimes clears all Calendar fields.
Example 2:
If I clear all fields in the model (variable vSetAnalysisIgnoreAll), it still doesn't work:
Sum({<$(vSetAnalysisIgnoreAll), Date={"02/11/2016"}>} Value)
Example 3:
But if I put 1 in Set Analysis so it ignores all by default, it works but that's not I want cause I need the calculation to be dynamic:
Sum({1<Date={"02/11/2016"}>} Value)
Are you using Date or DateNum for set analysis? What is Date field format here?
I tried with Date and with DateNum, it works the same in my examples.
Date is a date field in format DD/MM/YYYY
Are you making selections in other datefields which might be contradicting with your set analysis for any reason?
No, but I tried to clear them as well.
More than that, I cleared ALL FIELDS in the whole model
(this formula: Sum({<$(vSetAnalysisIgnoreAll), Date={"02/11/2016"}>} Value) )
and it didn't work.
Only if I clear user selections with "1" in set analysis, it works
(this formula: Sum({1<Date={"02/11/2016"}>} Value) )
but then it's not dynamic.
Create a variable in script load to find yesterday date like "=Today()-1" and use it to find out SUM of yesterday's sales."
You can also do this using an expression if your max(date) returns a value. First, check what is returning from max(Date)-1 and which format. The format of your filter selection date should be same as you have used in the expression.