topic Re: Values by Quarter in QlikView

Values by Quarter

Fri, 18 May 2012 13:05:07 GMT

Good day my friends,

I have the following situation, a calculated dimension with a condition, for example:

if(sum(Teus_OFF)/sum(OFF_Target)>=1,'30%','0')

I can see by month, if it reaches or not the target, putting the field month in the table.

Is there any possibility to create a table by Quarter, with the average of the 3 months, example if it reaches in Jan and Feb and not in March, it gives 30% for Jan and Feb and 0% for March, giving me the value of 20% that is the average of (30% + 30% + 0%)/3.

I wanna see the average of months by quarter, and not the sum in the quarter. Is it possible?

Basically what i want is show by quarter the average of jan-feb-mar results that shall be 20%

Let me know if i'm not being clear enough

Tks and Rgds,

Re: Values by Quarter

swuehl — Fri, 18 May 2012 13:24:35 GMT

It should be possible using advanced aggregation in your chart expression (or it should also be possible in the script if your results don't depend on selections):

I assume you have fields Month and Quarter (and Months are linked to their related Quarter).

So try creating a chart with dimension Quarter and as expression something along:

=avg(total<Quarter> aggr( if(sum(Teus_OFF)/sum(OFF_Target)>=1, 0.3, 0), Quarter, Month))

Re: Values by Quarter

Fri, 18 May 2012 13:41:55 GMT

Man, you are on fire!

Many thanks, worked perfectly.

Rgds, Jorge.