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: 
jessbanaga
Contributor II
Contributor II

How to display value from other columns in Pivot Table?

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. 

CustCategoryW47W48 W49W50W51W52W53
ATotalHours40202040404040
AAvgHours30402030303230.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

Labels (1)
3 Replies
edwin
Master II
Master II

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?

jessbanaga
Contributor II
Contributor II
Author

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.

 

edwin
Master II
Master II

look at this

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