Set Analysis help with working out what to include for QTR

[hopkinsc]

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.

[swuehl · Accepted solution]

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]

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]

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