How to calculate % change between 2 timeperiods when only one period is selected and shown as dimension

[rainer_leithner]

Hi Community,

i am struggling with a requirement looking simple on first sight but turned into some hairy problem for me.

In my datamodel theres a FACT table with basic information like Date, Material and Units.

I also use a TIME (Calendar) table with a few calculated periods for easier calculation and filtering. So far so good.

Because a few objects should be very generic concering the usage of TimeDimensions and the comparison of them, I linked a Table "ZeitDim" with the TIME table where the user can use the field "Timedimension" to choose the periods very generic.

For absolute values this is fine, but now I want to calculate the  relative change in % between the choosen Timeperiod and the Timeperiod defined as the correct related Timeperiod in the past.

E.g. if the user selects Timedimension='YTD', the values where Timedimension='YTD' should be divided by the values with Timedimension='YTD VJ'.

As a reference I prepared a taple (the suffix VP means previous period):

But only one or more of the Timedimensions (one or all 4) should be filtered from the user.

The wanted solution should look like that:

YTD VJ is not selected.

The not wanted standard looks more or less like that:

I tested and tried a lot with set analysis and alternate state but without luck and success.

It seems that QlikView always expects the selected values in a dimension for calculation.

Hope my requirement is clear decribed and one of you maybe has better ideas as I had .

Rainer

[rainer_leithner]

I tested it and it seems not to deliver a valid result:

When filtering on MAT and I have a look on material 10001 in my example, a Share of 0,11% will be calculated,

the share in MAT VJ is 0,14%

So the absolute differnence should be -0,03%, the relative one ist 78,5% respectively -21,4%.

BR,
Rainer

[sunny_talwar]

What is the exact expressions you are trying?

[rainer_leithner]

This is the expression I use in the Testversion (Calculate_Excluded_TimeDim_Upload_Share_Change.qvw).

=

((Sum([round(UNITS /22)])/Sum({<MATERIAL=>} TOTAL [round(UNITS /22)]))

/

After(Sum({<TimeDimension = p(TimeDimension)+p(TimeDimensionVP)>}[round(UNITS /22)])/Sum({<MATERIAL, TimeDimension = p(TimeDimension)+p(TimeDimensionVP)>} TOTAL [round(UNITS /22)])) -1) * Avg(1)

[sunny_talwar]

Try this

=((Sum([round(UNITS /22)])/Sum({<MATERIAL=>} TOTAL [round(UNITS /22)]))/

After(Sum({<TimeDimension = p(TimeDimension)+p(TimeDimensionVP)>}[round(UNITS /22)])/Sum({<MATERIAL, TimeDimension = p(TimeDimension)+p(TimeDimensionVP)>} TOTAL <TimeDimension> [round(UNITS /22)])) -1) * Avg(1)

[rainer_leithner]

Looks good when you select just one timedimension. but delivers strange results when selecting a second.or third one

Example:

Select only MAT: Gives you -18,27% -> looks good.

Select only YTD: Gives you -12,60% --> looks good

Select MAT and YTD: MAT is still correct with -18,27% but YTD is now -71,26% instead iof -12,60%...

[sunny_talwar]

Try this

=((Sum([round(UNITS /22)])/Sum({<MATERIAL=>} TOTAL <TimeDimension> [round(UNITS /22)]))/

After(Sum({<TimeDimension = p(TimeDimension)+p(TimeDimensionVP)>}[round(UNITS /22)])/Sum({<MATERIAL, TimeDimension = p(TimeDimension)+p(TimeDimensionVP)>} TOTAL <TimeDimension> [round(UNITS /22)])) -1) * Avg(1)

[rainer_leithner]

Hello Sunny,

that is exactly what I was looking for. Great! Many thanks for your support and time. All my issues are solved now.

BR,

Rainer