topic Re: Why my aggregation not working in expression in chart in QlikView

Why my aggregation not working in expression in chart

Wed, 01 Jul 2015 18:36:44 GMT

I am having a chart where i am showing the monthly trend .

in the chart if i am clicking on any month from calendar , my expression works fine which is using aggregation to show value

but does not work if i clear and select year and show for all month with the same expression

I am using Year-Month eg. 2015-Apr as my DImension and doing aggregation on a different field

Please suggest if i am missing something in my

Re: Why my aggregation not working in expression in chart

swuehl — Wed, 01 Jul 2015 18:45:39 GMT

Please post your expression.

Re: Why my aggregation not working in expression in chart

Thu, 02 Jul 2015 04:56:01 GMT

I am trying to get the Sum of duration_sec divided by total duration of Month for Model Keys with outage type 1

Sum({$<[outage_type]={1}} Aggr(Num((Sum({$<[outage_type]={1}>} duration_sec)/Num#(Interval(MonthEnd([Call_Year-Month])-MonthStart([Call_Year-Month]),'ss'))),'####.#####'),model_key))

This same expression gives desired output if we select a Month but does not gives correct value over overall trend

Re: Why my aggregation not working in expression in chart

jonathandienst — Thu, 02 Jul 2015 05:33:35 GMT

Here's your expression with the unnecessary formatting elements (Num, Interval) removed. This divides the summed durations by the number of seconds in the month range:

Sum(

Aggr(

Sum({$<[outage_type]={1}>} duration_sec)

((MonthEnd(Max([Call_Year-Month])) - MonthStart(Min([Call_Year-Month]))) * (24*60*60))

,model_key)

)

If you intend to divide by the number of days:

Sum(

Aggr(

Sum({$<[outage_type]={1}>} duration_sec)

(MonthEnd(Max([Call_Year-Month])) - MonthStart(Min([Call_Year-Month])))

,model_key)

)

Re: Why my aggregation not working in expression in chart

Thu, 02 Jul 2015 07:23:47 GMT

I added Call_Year-Month as dimension in aggr function and it worked. Thanks for suggestions