Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

conditional aggr

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...

1 Solution

Accepted Solutions
pat_agen
Not applicable

conditional aggr

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?

3 Replies
pat_agen
Not applicable

conditional aggr

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?

Not applicable

Re: conditional aggr

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.

Not applicable

Re: conditional aggr

thanks to pat again

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