topic Re: Last 3 month in straight table in QlikView

Last 3 month in straight table

blaise — Mon, 26 Nov 2012 09:41:03 GMT

Hello Qv'ers

I have a problem with showing a sum of the last X months in a straight table, where x i = 3.

I use the following expression to get the sum of the month;

sum(ReturnedItems)

in a straight table with Year and Month as dimensions (as seen in table below)

Now I would like a sum of the last 3 months, so the november value should include all ReturnedItems between 25aug2012 and 26nov2012.

The value for october should show sum of ReturnedItems between 26jul2012.

Year	Month	Returned Items	Returned Items last 3 months
2012	jul	1.000	1.000
2012	aug	2.000	3.000
2012	sep	3.000	6.000
2012	oct	4.000	9.000
2012	nov	5.000	12.000

The column returned items last 3 months shows what i want in the table at the 30th of Nov 2012, and with data from the 1st of Jul 2012.

I have tried with a set analysis ;

sum( {$<Year = , Month = , Week = , Date = {"<=$(=date(max(DateValid))) >=$(=date(addmonths(max(DateValid),-3))) "}>} ReturnedItems)

with the Total <allmost all combination of keys>, All and aggr together with rangesum none of them give me the result i want. Most often they only gives a result for sep, oct, and nov (and not the sum of the last 3 months from that date).

Thanks in advance

Re: Last 3 month in straight table

jagan — Mon, 26 Nov 2012 12:28:08 GMT

Hi,

Try Chart Properties -> Expressions -> Accumulation -> Accumulate 3 steps back

This will work for you

Regards,

Jagan.

Re: Last 3 month in straight table

blaise — Mon, 26 Nov 2012 12:29:16 GMT

Rangesum is sadly not an option since all months needs to be "shown" in the table.

When the user selects year = 2012 the value for Jan-2012 must be the value for Nov-2011 -> Jan-2012 and not only Jan-2012.

Can't see why this requirement should be impossible...

Re: Last 3 month in straight table

blaise — Mon, 26 Nov 2012 12:31:49 GMT

same issues as with the use of rangesum(above()).