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

Result from month based on previous months

Hi,

We are struggling with set analysis trying to calculate a result for a specific month based on values from previous months. For sake of simplicity we boiled this down to its essentials.

Consider the following table:

 

ReportingMonthMonthYearThis MonthPrev Month
0Oktober 20164478680
1September 2016424493,990
2Augustus 20163925330
3Juli 20162868350
4Juni 20163968990
5Mei 20163550930
6April 20163274920
7Maart 20163268110
8Februari 20164457980
9Januari 20164005350
10December 20152380790
11November 2015268218,670
12Oktober 2015330089,610

In the 'Prev Month' column (where currently only zero's are displayed) we want the value from the next row to appear. We tried doing so using set analysis as follows:

Sum({$<ReportingMonth={"$(=Test2)+1"}>}InvoiceQuantity)

Test2 is a LET variable: LET Test2 = 'ReportingMonth + 1';

The above table should look as follows:

 

ReportingMonthMonthYearThis MonthPrev Month
0Oktober 2016447868424493,99
1September 2016424493,99392533
2Augustus 2016392533286835
3Juli 2016286835396899
4Juni 2016396899355093
5Mei 2016355093327492
6April 2016327492326811
7Maart 2016326811445798
8Februari 2016445798400535
9Januari 2016400535238079
10December 2015238079268218,67
11November 2015268218,67330089,61
12Oktober 2015330089,61398637

We have gone through a whole bunch of variations as well as through a miriad of posts on the community. No luck and pretty frustrating. So any help will be highly appreciated.

Thanks!

10 Replies
antoniotiman
Master III
Master III

Hi,

Temp:
Load * Inline [
ReportingMonth, MonthYear, ThisMonth
0, Oktober 2016, 447868 
1, September 2016, 424493.99 
2, Augustus 2016, 392533 
3, Juli 2016, 286835 
4, Juni 2016, 396899 
5, Mei 2016, 355093 
6, April 2016, 327492 
7, Maart 2016, 326811 
8, Februari 2016, 445798 
9, Januari 2016, 400535 
10, December 2015, 238079 
11, November 2015, 268218.67 
12, Oktober 2015, 330089.61 
]
;

Load *,Peek(ThisMonth) as PrevMonth
Resident Temp
Order By ReportingMonth desc;
Drop Table
Temp;

Regards,

Antonio

giakoum
Partner - Master II
Partner - Master II

in a chart expression, use the above or below function.

Not applicable
Author

I'm sorry, but this is not what we are looking for. We need to aggregate taking filters into account. We cannot calculate these measures during data load.

Not applicable
Author

This looks interesting, but for some reason, I'm not able to get this to work. This is the code used, and some screenshots to clarify.

Above(Sum(InvoiceQuantity),1)

Screenshot_1.png

Screenshot_2.png

rubenmarin

Hi Jeroen, as Ioannis said, you can do it in chart using the below funtion:

Below(Sum(InvoiceQuantity))


The option given by Antonio is also a good one, sometimes is better have the previous values stored in the same row.

As a general comment, you tried yo dinamically use set analysis to filter ReportingMonth based on row values, this can't work because set analysis is applied before the table is calculated, so vTest will return null because it has many different values (row value not affecting). And in case it only has one value, this value will be applied to all rows (the same value)

antoniotiman
Master III
Master III

Hi Ruben,

I think You need TOTAL in expression

Below(TOTAL Sum(InvoiceQuantity))

Regards,

Antonio

Not applicable
Author

Hi Ruben ,

The replies we received so far made us realize that we probalbly oversimplified this a little.

  • First off in the PrevMonth we actually need to sum the 'ThisMonth' values from the previous 12 months.So for October 2016 we need to calculate the sum of the values for the months September 2016 up until October 2015. This outcome is then used in a subsequent calculation step where is used as a denominator for dividing the Present value (which is the numerator) (but no need to get into those details).
  • Second, the user may slice on different dimensions like (product family, customer region, customer, etc...). The resulting measure which we are calculating should honor the filter selections set by the user of course. While it may be possible to calculate and store the sum of the previous 12 months at the atomic grain level it does not feel like an attractive approach.

Hope this helps to clarify a bit more.

Thanks for your help!

Not applicable
Author

Thanks Antonio, with this answer we are one step closer as we are getting the correct results now. But as we mentioned below, the problem is slightly more complex than this...

Still, thanks for your suggestion!

Anonymous
Not applicable
Author

We found this post to be very helpful: Accumulative Sums