topic Re: How to display value from other columns in Pivot Table? in QlikView

How to display value from other columns in Pivot Table?

jessbanaga — Fri, 15 Nov 2024 23:28:04 GMT

Hi,

Is it possible to get the sum or average of previous columns and display it in the current and future columns. Basically, the columns are number of weeks of the year.

Cust	Category	W47	W48	W49	W50	W51	W52	W53
A	TotalHours	40	20	20	40	40	40	40
A	AvgHours	30	40	20	30	30	32	30.3

Currently we are at Week 50 (W50) in the pivot, the value to reflect in W50 of AvgHours Category should be Average hours of W47, W48, and W49 thereby W50 = 30 (in bold and italic).

Same goes with the succeeding weeks. W51 value should be average of of W47, W48, W49, and W50. And so on...

The Total Hours is SUM(Hours) only will work. But the AvgHours is very challenging is it scoped with the week headers.

Do you have an idea?

TIA

Re: How to display value from other columns in Pivot Table?

edwin — Fri, 11 Dec 2020 20:13:10 GMT

you can do that in the expression - not referencing the columns but in your expression you indicate which weeks to average. you may also need to bridge the weeks going multiple weeks back

also how will the average for weeks 47 to 49 be calculated? it appears the average is cumulative. how about weeks before week 47?

Re: How to display value from other columns in Pivot Table?

jessbanaga — Sat, 12 Dec 2020 07:25:30 GMT

Week 47 to 49 shouldn't be shown. The table I created is on for purposes showing it in details.

I tried:

SUM({< [Week] = {"=[Week] > $(vCurrentWeek)} >}Aggr(AVG({< [Week] = {"=[Week] >= $(vStartWeek) AND [Week] < $(vEndWeek)"}>} [Hours]), [Cust],[Week]))

Where: declared as variables look like below:

vCurrentWeek = Week(Today())

vStartWeek = Week(Today()-3)

vEndWeek = [Week]

But it gives me 0.

Re: How to display value from other columns in Pivot Table?

edwin — Sat, 12 Dec 2020 12:12:02 GMT

look at this

it has provision for historical weeks but if you just need current week going forward you just use CUMULATIVE datetype