33 Replies Latest reply: Nov 23, 2016 11:34 AM by Linoy Elias

# Set analysis - calculate yesterday's sales

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

Try this:

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

• ###### Re: Set analysis - calculate yesterday's sales

I tried that.

Unfortunately, that doesn't work either...

• ###### Re: Set analysis - calculate yesterday's sales

try

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

• ###### Re: Set analysis - calculate yesterday's sales

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

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

• ###### Re: Set analysis - calculate yesterday's sales

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

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

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

What is this? \$(vSetAnalysisIgnoreAll)

• ###### Re: Set analysis - calculate yesterday's sales

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

• ###### Re: Set analysis - calculate yesterday's sales

Would you be able to show the expression behind it?

• ###### Re: Set analysis - calculate yesterday's sales

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

• ###### Re: Set analysis - calculate yesterday's sales

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

• ###### Re: Set analysis - calculate yesterday's sales

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

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

what is ", Date" ?

• ###### Re: Set analysis - calculate yesterday's sales

Didn't you say you have Date and DateNum fields in your app. Not sure where you make selection, but assuming you make selection in Date field, does this work?

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

• ###### Re: Set analysis - calculate yesterday's sales

No, it still shows 0

• ###### Re: Set analysis - calculate yesterday's sales

That is very strange. Would you be able to share a sample where this isn't working?

• ###### Re: Set analysis - calculate yesterday's sales

Attached

• ###### Re: Set analysis - calculate yesterday's sales

What is the issue with this expression?

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

I selected 05/05/2011 and I am seeing both current date and previous date amounts. Are you not seeing this when you open the attached?

• ###### Re: Set analysis - calculate yesterday's sales

Indeed it works in the sample app.

But not in my production app!

I really don't know why.

I'll try to load previous date in script calendar like arvind654 suggested

• ###### Re: Set analysis - calculate yesterday's sales

Another approach like this might help

The As-Of Table

• ###### Re: Set analysis - calculate yesterday's sales

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

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

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

I usually try to create flags in Master for easy work...

You can maybe create a flag in Master Calendar

 InDay(Date, today(),-1)*-1 as PD,

And use

Sum({<PD={1}}>} Value)

• ###### Re: Set analysis - calculate yesterday's sales

Will it be dynamic in UI?

It seems that you always look at today() and compare to it and it's wrong.

I don't need yesterday's date value but the previous date value.

If a user chooses 22/11/2016 so I need to show 21/11/2016's value.

If he chooses 21/11/2016 so I need to show 20/11/2016's value.

• ###### Re: Set analysis - calculate yesterday's sales

Yes it will give you Yesterday's data not Previous Day data.

• ###### Re: Set analysis - calculate yesterday's sales

Try this one in Master Calendar

Day(Date-1) as PreviousDay, <<< for Only Day

Date([Date-1,'DD/MM/YYYY') as PreviousDate <<<< Gives you yesterday's date.

and you can use PreviousDate in front end

• ###### Re: Set analysis - calculate yesterday's sales

Thanks, I'll try that

• ###### Re: Set analysis - calculate yesterday's sales

I'm not sure if it will give you what you looking for as it's not a flag but just Dates or Day...

I cannot imagine a valid scenario..

I'll see if I can create a flag

• ###### Re: Set analysis - calculate yesterday's sales

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)