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

Help with set analysis based on dimensions

Hi, I'm trying to forecast sales based on historical trends, but am having problems 'shifting' historical results to generate a forecast.  I have this app that shows the original sales of customers who've come on in June, what their original sales were when they came on file and what their sales were in Aug. 2014.  So for example customers who've come on in June 2014 generated $1,214,678 when they came on file, and in Aug. 2014 they generated $65,148 or 5.36% of their original sales. 

Capture.PNG

What I'd like to do is shift that 5.36% down so it appears on the '2015 Jun' row so I can multiply it by the $1,199,756 orig sales from customers who've come on in 2015 to forecast how much they'll make in Aug. 2015.  All the other percentages should also shift downward a year.  For the oldest customers in 2012 I'd like to knock back their percentage by 10%.  So the table should look like this (color added for clarity):

Capture2.PNG

I thought I could do this with set analysis using the dimension as a variable but it it's not working.  I've attached the app.  Thanks in advance for any help or guidance.


Jim

2 Replies
swuehl
MVP
MVP

Set analysis is evaluated once per chart, not per dimension value.

But you can probably use chart inter record functions, like above():

=above(TOTAL Sum({$<Year={'2014'}>}Sales) ) /above(TOTAL Sum(FTBSales))

I haven't fully understood your sample for 2012, but you can probably create your value using chart inter record functions, too.

Not applicable
Author

Thanks swuehl, that's exactly what I was looking for!  Really appreciate your help, you saved me tons of time.