Aggregate sum of quarter months and variance in Qliksense table

[richard24best]

Hi Team,

I have a requirement where we can collating Client metrics (combination of numbers and percentage) monthly.

But i want to show Monthly values based on Month selection and Aggregated quarterly (sum of monthly values for that quarter and average percentage of the quarter) when Quarter is selected by the user.

Could you please help me out how to write expression to get this?

1) Month wise

2) Quarterly aggregate sum and average of percentage (Quarter - Jan, Feb, Mar)

3) Quarterly variance

sample table attached

[Daniel_Castella]

Hi @richard24best 

I would like to help you on this request, but I'm not understanding your data sample. Is this the data you are expecting to have or is it the raw data?

-In the first case, could you, please provide a sample of row data in order I can implement the calculations?

-In the second case, could you, please, indicate how are you obtaining this data, specially the percentages, and which are the results you expect?

Also, could you, please, clarify which Qlik object do you want to use and if the Monthly and Quarterly data need to be displayed together?

Thank you very much

Daniel

[Chanty4u]

Try this for sum you can try for AVG in place of sum and then difference 

Sum(

  Aggr(

    Sum(ValueMetric),

    Year,

    Quarter,

    Month

  )

)

[richard24best · Accepted solution]

Thanks @Daniel_Castella  and @Chanty4u  for your time and support.

Well, i have figured out self :). Sharing expression for latest quarter values

If(Only([Metric Type])='Percentage',
Num(Avg({<YearQuarter_Sort={"$(=if(GetSelectedCount(YearQuarter)=0 and GetSelectedCount(Month)=0,Max(Total YearQuarter_Sort)=1,Max(YearQuarter_Sort)))"}>}Quarter_Value),'0.0%'),
Num(Sum({<YearQuarter_Sort={"$(=if(GetSelectedCount(YearQuarter)=0 and GetSelectedCount(Month)=0,Max(Total YearQuarter_Sort)=1,Max(YearQuarter_Sort)))"}>}Quarter_Value),'#,##0'))