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: 
Not applicable

Get previous dates sum

Hello fellow QlikViewers!
I am trying to get the sum for the previous date in a chart, but it's not working. You can see the result in the attachement.
In the example one can see a date field together with the number of orders that specific date. Then I want to output the number of orders the day before. As you can see, that column does not work... It should look like the one after, called "Goal". That one is using the above() function, but since I will use this value in an aggregated expression later on, that solution doesn't work. The expression behind my failed column looks like this:
sum({<[Order Date] = {"$(=date(max([Order Date])-1))"}>} [Order Count])
Can someone please help me?
Thank you,
Stefan
Order   DateNo of OrdersPrev Date No of   OrdersGoal
2012-01-0130-
2012-01-0260003
2012-01-031 1730600
2012-01-0499901 173
2012-01-058600999
2012-01-063100860
2012-01-071450310
2012-01-098280145
2012-01-081140828
2012-01-108870114
2012-01-111 4840887
2012-01-1260501 484
2012-01-131 6900605
2012-01-1453301 690
2012-01-162 2710533
2012-01-1526202 271
2012-01-171 4690262
2012-01-182 08001 469
2012-01-191 23302 080
2012-01-2091501 233
2012-01-215970915
2012-01-231 6970597
2012-01-2227401 697
2012-01-241 1930274
2012-01-252 93201 193
2012-01-261 00902 932
2012-01-2792901 009
2012-01-286400929
2012-01-292640640
2012-01-30955955264
2012-01-311 1380955
1 Solution

Accepted Solutions
Not applicable
Author

I got it to work by creating a separate table "previous date", where the key was based on the date. But in the key value in the "previous date" table, the date was added one day. This way, the value in the previous table would be linked to another date in the normal table. And with that, I can access the number for the previous day while still using a regular calendar. I guess not all solutions can be made in the expressions alone

View solution in original post

5 Replies
Anonymous
Not applicable
Author

Stefan,

You can use above() function:
above(sum([Order Count]))

Regards,
Michael

Not applicable
Author

No, unfortunately not. In the end I won't even have a column for the number of orders, they are just used in an expression. I have to aggregate over each date and calculate stuff, but in the end they will be applied for week/month dimensions. So even if I apply the above function here, when I switch the dimension to Month instead it won't work.

Not applicable
Author

Hi Stefan,

as far as i know you cant use a set analysis within the "row context", because it is evaluated just once per chart. Depending on what you want to achieve in the end you probably have to find a workaround (removing date dimension to work with sets, above() + rangesum functions/combos, 2 charts side by side etc)

greets,

Max

Not applicable
Author

Alright, bummer. Maybe I'll have to fix it in the data layer, creating a field for previous dates orders.Thanks for the clarification.

Not applicable
Author

I got it to work by creating a separate table "previous date", where the key was based on the date. But in the key value in the "previous date" table, the date was added one day. This way, the value in the previous table would be linked to another date in the normal table. And with that, I can access the number for the previous day while still using a regular calendar. I guess not all solutions can be made in the expressions alone