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]

What is this? $(vSetAnalysisIgnoreAll)

[linoyel]

It's variable that clears ALL FIELDS chosen by user in the model

[linoyel]

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

[sunny_talwar]

Would you be able to show the expression behind it?

[linoyel]

='['&concat(distinct{$<$Field={'*'}>}$Field,']='&',[')&']='

[linoyel]

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).

[sunny_talwar]

Is there a reason you use Concat($Field) instead of 1 to ignore all selections?

[linoyel]

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!

[sunny_talwar]

So this won't work if you make selection in Date field?

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

[linoyel]

what is ", Date" ?