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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Set Analysis help with working out what to include for QTR

Hi all,

I need some help with some complicated set analysis (or i think its complicated anyway!)

basically, i have an expression for Sales. it includes Actual and Forecast

one of my time selections is QTR, and its this thats giving me a headache.

I have the following variables..

vCurrentPeriod - Holds the current period

vPV1_Period - this holds current period +1

vPV2_Period - this holds current period +2

vPV3_Period - this holds current period +3

vQtrStart - Holds the start period number of the current Qtr

vQtrEnd - Hold the end period of the current Qtr


These are the lines of my expression..


This gives the sum of sales for actuals where the period is <=CurrentPeriod and >=QtrStart

sum({<DataSet={'Actuals'},Period={"<=$(=vCurrentPeriod)>=$(=vQtrStart)"}>}Value)

This gives the sum of PV1 where the period is >CurrentPeriod and less than the vPV1_Period variable

sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV1_Period)"}>}PV1)

This gives the sum of PV2 where the period is >CurrentPeriod and less than the vPV2_Period variable

sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV2_Period)"}>}PV2)


This gives the sum of PV3 where the period is >CurrentPeriod and less than the vPV3_Period variable

sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV3_Period)"}>}PV3)


So the above lines of code are needed foe my expression, but for vertain periods i don't need all of the above.

e.g.


If the current period is 01, then my expression needs to be..

sum({<DataSet={'Actuals'},Period={"<=$(=vCurrentPeriod)>=$(=vQtrStart)"}>}Value)

+

(sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV1_Period)"}>}PV1)

+

sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV2_Period)"}>}PV2))


so the above broken down is..

Sales for Period 01 + PV1 (which is Period 02) + PV2 (which is Period 03)


I dont need the last line as this would include data for period 04 but that will mean it will go out of the current QTR number of 1


if the current period is 04, (meaning the current qtr is QTR 2 and consists of Periods 04-07) then the expression needs to be 

sum({<DataSet={'Actuals'},Period={"<=$(=vCurrentPeriod)>=$(=vQtrStart)"}>}Value)

+

(sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV1_Period)"}>}PV1)

+

sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV2_Period)"}>}PV2)

+

sum({<DataSet={'Forecast'},Period={">$(=vCurrentPeriod)<=$(=vPV3_Period)"}>}PV3))


broken down it would be actual sales from periods 04 + PV1 (period 05) + PV2 (period 06) + PV3 (period 07)


I know i can do this using an IF statement but that will make the expression huge as all of the above is already in a nested if statement, so whatever the outcome of the above solution will be, it will need to be replicated 7 times within the same expression.


I was wondering if there was something i can do within the variable for the vQtrEnd (or another variable) to correctly determine which of the PV fields to include in the sum.


any help or suggestions will be appreciated.


see example attached.


Thanks in advance.


1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe something like this

sum({<DataSet={'Actuals'},Period={"<=$(=vCurrentPeriod)>=$(=vQtrStart)"}>}Value)

+

(sum({<DataSet={'Forecast'},Period={"=Period>$(=vCurrentPeriod) and Period<=$(=RangeMin(vPV1_Period,vQtrEnd))"}>}PV1)

+

sum({<DataSet={'Forecast'},Period={"=Period>$(=vCurrentPeriod) and Period<=$(=RangeMin(vPV2_Period,vQtrEnd))"}>}PV2)

+

sum({<DataSet={'Forecast'},Period={"=Period>$(=vCurrentPeriod) and Period<=$(=RangeMin(vPV3_Period,vQtrEnd))"}>}PV3))

View solution in original post

3 Replies
swuehl
MVP
MVP

Maybe something like this

sum({<DataSet={'Actuals'},Period={"<=$(=vCurrentPeriod)>=$(=vQtrStart)"}>}Value)

+

(sum({<DataSet={'Forecast'},Period={"=Period>$(=vCurrentPeriod) and Period<=$(=RangeMin(vPV1_Period,vQtrEnd))"}>}PV1)

+

sum({<DataSet={'Forecast'},Period={"=Period>$(=vCurrentPeriod) and Period<=$(=RangeMin(vPV2_Period,vQtrEnd))"}>}PV2)

+

sum({<DataSet={'Forecast'},Period={"=Period>$(=vCurrentPeriod) and Period<=$(=RangeMin(vPV3_Period,vQtrEnd))"}>}PV3))

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Stefan,

That looks good in my sample, i will apply it to my real data and test

Thanks so much!

Could you explain how the RangeMin works please?

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

its ok, ive looked in the help file thanks again!