Set analysis - calculate yesterday's sales

[linoyel]

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?

[sunny_talwar]

Try this:

Sum({<DateNum={"$(=Max(DateNum)-1)"}>} Value)

[linoyel]

I tried that.

Unfortunately, that doesn't work either...

[shraddha_g]

try

Sum({<DateNum={"$(=num(Max(DateNum)-1))"}>} Value)

[linoyel]

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)

[sunny_talwar]

Are you using Date or DateNum for set analysis? What is Date field format here?

[linoyel]

I tried with Date and with DateNum, it works the same in my examples.

Date is a date field in format DD/MM/YYYY

[sunny_talwar]

Are you making selections in other datefields which might be contradicting with your set analysis for any reason?

[linoyel]

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.

[ramkrishna86]

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.