Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
linoyel
Specialist
Specialist

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?

1 Solution

Accepted Solutions
linoyel
Specialist
Specialist
Author

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!!!

View solution in original post

33 Replies
sunny_talwar

Try this:

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

linoyel
Specialist
Specialist
Author

I tried that.

Unfortunately, that doesn't work either...

shraddha_g
Partner - Master III
Partner - Master III

try

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

linoyel
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Creator II
Creator II

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.