Ignore dimension but not selection issue in Straight Table Qlikview

[ari_c95]

Hi All, 

I have a Straight table with multiple dimensions in Qlikview. The dimension most relevant here is the Month. Essentially, for each month (and the other dimension, named DIM below), I have a Sales value. What I need to do is calculate a new value summing each dimension value's Sales for the current month and the previous month. So it should look something like this. 

Month	DIM	Sales Value	Calculated Value
December	A	100	400 (100+300)
December	B	200	600 (200+400)
November	A	300	800 (300+500)
November	B	400	1000 (400+600)
October	A	500	1200 (500+700)
October	B	600	1400 (600+800)
September	A	700	(blank – no prev month)
September	B	800	(blank – no prev month)

The main problem I can't figure out a way around is that this Straight table will be affected by a Month Selection filter, where if you select October, the table should only show the 2 October rows. However, the calculation must still fetch the September values from the model and get you the added value. 

Any idea how I could progress with this? I've tried my hand at using TOTAL and Set Expressions, but to no avail. 

Any help would be highly appreciated. 

Thanks!

[sunny_talwar]

Have you tried this

=Aggr(If(RowNo() > 1, RangeSum(Above(Sum(Sales), 0, 2))), DIM, (Month, (NUMERIC)))

[ari_c95]

Hi Sunny, couple of things to note here.

1. Using Above requires that the previous month row is also present in the table. For example, if we use above for December, the row for November needs to be present. That is not the requirement here, only the December row must be shown and yet fetch the data for November. 

2. The number of dimensions used with the Month is dynamic (here I've only shown 1 - Dim). But the number of these dimensions can range anywhere between 0 to 15 (depending on a filter selection). 

Hence, does not seem like your solution will work. Any other alternatives you can think of?

[lanlizgu]

First of all, you would need to use also the year so you could have the date (you are not able to convert a month into date if you don't have the year you are refering to). Once you have this information you are able to select the previous month with addmonths expression.

[sunny_talwar · Accepted solution]

The best alternative would be to use The As-Of Table 

[ari_c95]

The Month column is already a date table. The problem with this is that a dimension context is being applied according to the rows and the set expression (which contains a reference to the previous month) is not working properly. 

[ari_c95]

Yes this looks like something that might work. Thanks, I will try this out. 

[Brett_Bleess]

Ari, be sure to circle back to the post and use the Accept as Solution button on the post(s) that actually helped you resolve your question.  I would have done it for you, but I was not sure which of the posts actually helped given the sequence of things.  This gives credit to the posters and lets other Community Members know which thing(s) actually helped.

Regards,
Brett