Qlik Sense App Development

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
New Contributor II

Need help in calculation

Hello All,

I have a pivot table where my

1.  "Column 1" dimension in Rows

2.  "Quarter"  dimension in Column. This changes dynamically based the date selected.It will  always display 3 quarters.

if(Date >='\$(vRolling3QtrStartDate)' and Date<= '\$vRolling3QtrEndDate)', Quarter)

3.    Measure is an calculation =  Sum(X)/Sum(total(X).

 Q1 Q2 Q3 XYZ1 2% 1% 2% XYZ2 5% 7% 6% XYZ3 10% 8% 8% XYZ4 3% 2% 5%

I need some help in calculating my measure,  where i  can display the measure as a Weighted Average of all the 3 months that falls under particular quarter. Say suppose if I select a date 11/25/2016. I will display 3 completed quarters as Q1, Q2, Q3 of 2016.

and i want the measure for Q1 and XYZ1 combination as weighted average of all the 3 months that fall under that Q1.

Any ideas or suggestions? I hope i haven't confused.

3 Replies
MVP & Luminary

Re: Need help in calculation

Perhaps this: avg(aggr(Sum(X)/Sum(total X),Month, [Column 1]).

Make sure to use the correct field names; Month and [Column 1] are just examples based on what you posted.

talk is cheap, supply exceeds demand
New Contributor II

Re: Need help in calculation

Thanks for that.

However there is a some change in the requirement itself now. Instead of displaying the weighted average of the measure for the months that are falling under that particular quarter, now  i need to display the measure value only for the last month of that particular quarter. Ex: In Q1 i need to display the value of March, for Q2  it is June, Q3 it is September and Q4  it is December. Any ideas.

MVP & Luminary

Re: Need help in calculation

You could filter the months with a bit of set analysis if you have a numeric month field. sum({<Month={"=Mod(Month,3)=0"}>}X).

talk is cheap, supply exceeds demand
Community Browser