topic conditional aggr in QlikView

conditional aggr

Thu, 16 Jun 2011 05:45:16 GMT

i have a line chart that shows the average sales over the past X number of weeks... in my current case it's 26 weeks or 182 days...

one of the lines will show the overall sales while the other lines are location-specific

the expression for the overall line works fine

=aggr((calculation formula), date)

however if i add

=aggr(if(location='A', (calculation formula)), date)

the line simply disappears

in case u're wondering why im using date as the group, all records belonging to the same set are inserted at the same timing..

this is the dimension of the chart

=if(date>=Date(Today()-182) and date<=Date(Today()), workweek)

so i actually have the the current/latest week at the right end of the axis followed by the past 26 weeks...

please advise if nested the if-condition in the wrong way or there's a proper way around this...

conditional aggr

pat_agen — Thu, 16 Jun 2011 06:25:41 GMT

hi,

normally the way you would use and if() clause in a calculation is as follows:

[for the example I'll say your calculation forula = sum(sales)]

sum(sales)

becomes

sum(if(location='A', sales)).

so maybe you shoudl go from

aggr(sum (sales),date)

aggr(sum(if(location='A',sales)),date)

does that do the trick?

Re: conditional aggr

Thu, 16 Jun 2011 06:48:54 GMT

hi pat thanks for the help...

this is the expression after adding in your suggestion, unfortunately it does not do the trick... the calculation goes wrong instead...

=aggr(

(sum(if(Site='A', field1))

/sum(if(Site='A', field2)))*

(sum(if(Site='A', field3))

/sum(if(Site='A', field4)))*

(sum(if(Site='A', field5))

/sum(if(Site='A', field6)))

, date)

i tried using set analysis instead of aggr, it works but the graph does not adjust itself according to selections

=(sum

({$<Site ={A}>} field1)

/sum({$<Site ={A}>} field2))*

(sum({$<Site ={A}>} field3)

/sum({$<Site ={A}>} field4))*

(sum({$<Site ={A}>} field5)

/sum({$<Site ={A}>} field6))

this was the original expression,

=aggr(

if(Site='A',

(sum(field1)

/sum(field2))*

(sum(field3)

/sum(field4))*

(sum(field5)

/sum(field6))

)

, date)

oh and to add on, the date is actually part of a synthetic key that was automatically created by QV because i linked that field to the same field of a calendar table since QV's week system is different from what we want. not sure if this was part of the original problem though.

with the original expression which did not appear on the chart, if i changed the date to another date field (for e.g. lastUpdated), the line actually appears but that isn't actually the group condition we want since we want to group by the record insertion date and follow our own calendar.

Re: conditional aggr

Thu, 16 Jun 2011 07:24:47 GMT

thanks to pat again

please ignore my previous post, i used the wrong field to aggregate