topic Re: Set analysis - calculate yesterday's sales in Visualization and Usability

Set analysis - calculate yesterday's sales

linoyel — Tue, 22 Nov 2016 13:56:20 GMT

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?

Re: Set analysis - calculate yesterday's sales

sunny_talwar — Tue, 22 Nov 2016 14:07:15 GMT

Try this:

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 09:17:55 GMT

I tried that.

Unfortunately, that doesn't work either...

Re: Set analysis - calculate yesterday's sales

shraddha_g — Wed, 23 Nov 2016 09:35:40 GMT

try

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 10:09:38 GMT

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)

Re: Set analysis - calculate yesterday's sales

sunny_talwar — Wed, 23 Nov 2016 10:29:28 GMT

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 10:48:46 GMT

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

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

Re: Set analysis - calculate yesterday's sales

sunny_talwar — Wed, 23 Nov 2016 10:51:08 GMT

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 10:53:44 GMT

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.

Re: Set analysis - calculate yesterday's sales

ramkrishna86 — Wed, 23 Nov 2016 10:54:10 GMT

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.

Re: Set analysis - calculate yesterday's sales

sunny_talwar — Wed, 23 Nov 2016 10:56:05 GMT

What is this? $(vSetAnalysisIgnoreAll)

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 11:06:06 GMT

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 11:09:43 GMT

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

Re: Set analysis - calculate yesterday's sales

sunny_talwar — Wed, 23 Nov 2016 11:12:02 GMT

Would you be able to show the expression behind it?

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 11:22:00 GMT

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 11:29:29 GMT

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

Re: Set analysis - calculate yesterday's sales

sunny_talwar — Wed, 23 Nov 2016 12:15:05 GMT

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 12:27:46 GMT

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!

Re: Set analysis - calculate yesterday's sales

sunny_talwar — Wed, 23 Nov 2016 12:45:43 GMT

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

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

Re: Set analysis - calculate yesterday's sales

linoyel — Wed, 23 Nov 2016 14:57:22 GMT

what is ", Date" ?