Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
spavankumar1980
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).

 

Q1Q2Q3
XYZ12%1%2%
XYZ25%7%6%
XYZ310%8%8%
XYZ43%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
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
spavankumar1980
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
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