
conditional aggr
Patrick Laredo Jun 16, 2011 2:25 AM (in response to AXON SG)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)
to
aggr(sum(if(location='A',sales)),date)
does that do the trick?

Re: conditional aggr
AXON SG Jun 16, 2011 2:57 AM (in response to Patrick Laredo)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
AXON SG Jun 16, 2011 3:24 AM (in response to AXON SG)thanks to pat again
please ignore my previous post, i used the wrong field to aggregate

