topic Get previous dates sum in QlikView

Get previous dates sum

Tue, 24 Apr 2012 18:20:13 GMT

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 Date	No of Orders	Prev Date No of Orders	Goal
2012-01-01	3	0	-
2012-01-02	600	0	3
2012-01-03	1 173	0	600
2012-01-04	999	0	1 173
2012-01-05	860	0	999
2012-01-06	310	0	860
2012-01-07	145	0	310
2012-01-09	828	0	145
2012-01-08	114	0	828
2012-01-10	887	0	114
2012-01-11	1 484	0	887
2012-01-12	605	0	1 484
2012-01-13	1 690	0	605
2012-01-14	533	0	1 690
2012-01-16	2 271	0	533
2012-01-15	262	0	2 271
2012-01-17	1 469	0	262
2012-01-18	2 080	0	1 469
2012-01-19	1 233	0	2 080
2012-01-20	915	0	1 233
2012-01-21	597	0	915
2012-01-23	1 697	0	597
2012-01-22	274	0	1 697
2012-01-24	1 193	0	274
2012-01-25	2 932	0	1 193
2012-01-26	1 009	0	2 932
2012-01-27	929	0	1 009
2012-01-28	640	0	929
2012-01-29	264	0	640
2012-01-30	955	955	264
2012-01-31	1 138	0	955

Get previous dates sum

Anonymous — Tue, 24 Apr 2012 19:38:01 GMT

Stefan,

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

Regards,
Michael

Re: Get previous dates sum

Wed, 25 Apr 2012 07:06:17 GMT

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.

Get previous dates sum

Wed, 25 Apr 2012 07:22:34 GMT

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

Get previous dates sum

Wed, 25 Apr 2012 07:27:17 GMT

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

Re: Get previous dates sum

Wed, 25 Apr 2012 09:38:39 GMT

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