Help with set analysis (previous period)

[mike_spada]

In QlikView I imported a table with 3 columns: Name, PeriodID, Hours.

I want a pivot with Name and sum of hours for PeriodID equal to the previous PeriodID of the selected period.

For example I select PeriodID 114 and I want to have the sum of hours for Period 113 (114-1).

 I thought to know how to do it... I'm pretty sure that I could previously but now I'm going crazy. Please help...

Thanks in advance.

M.

[Qrishna]

Sorry i cant open you file. could you please put the code heree.

[DanielleW1]

Hi,

We do this quite often when looking at current year vs previous year.

If you try this it should work:

Sum ({$<PeriodID={'$(=[PeriodID]-1)'} > }[Hours])

[Qrishna]

usually in tables we wrap it in a Aggr:

Sum(Aggr(only({$<PeriodID={'=$(vPeriod)}'>}Hours), PeriodID)) where vPeriod = PeriodID - 1 or 

Sum(Aggr(Sum({$<PeriodID={$(=PeriodID - 1)}>} [Hours]),PeriodID)). if that doesnt work, you could do somehting like below:

data:
load *, PeriodID - 1 as PreviousPeriodID;
load * inline [
Name, PeriodID, Hours
Y,112,6
Y,113,7
Y,114,8
];

Qualify *;
GivenData:
Load * resident data;
UnQualify *;

Left Join (data)
LOAD
PeriodID as PreviousPeriodID,
[Hours] as PreviousHours
Resident data;

[mike_spada]

It works in the sample I posted, but it doesn't work in my app, a little bit complex 😞

M.

[Kushal_Chawda]

@mike_spada  You can use below measure to get previous period value in pivot table. Assuming your period field is numeric.

sum(aggr(above(Sum({<Period>}Hours),1),Name,(Period,(NUMERIC))))

if your period is not numeric, try below

sum(aggr(above(Sum({<Period>}Hours),1),Name,Period))