Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Can I add set analysis to group by field inside Aggr()?

Hi,

I have a very regular score calculation which the logic like: Score= sum(Numerator * Weight) / sum(Weight) for all measures. Those numerator and weight are for each measure. So, I have to group by MeasureID. In order to compare, I have to show current year score and prior year one. Now I met a problem, measures may change from year to year.  In my example, there are 3 measures in 2014 but only two in 2014. So, when calculated 2015 and 2014 score at the same time, 2014 score only takes 2 measures same as 2015. 

When 2014 is selected, we can see the score is 8.7, but when 2015 is selected, the prior score which is 2014 shows 7.8. This is because only two 2015 measures are counted for 2014, although it took correct 2014 data by set analysis.

So, the aggr function for prior year score showing below, how can I take all possible measures for 2014?

round(
sum(Aggr(sum({<Year={$(=only(Year)-1)}>} Numerator)*only({<Year={$(=only(Year)-1)}>} Weight), MeasureID))
/
Sum(Aggr(Max({<Year={$(=only(Year)-1)}>}Weight) , MeasureID))
,0.1)

Thanks in advance.

1 Solution

Accepted Solutions
Highlighted

Hi Fion, you can try the last year expression as:

round(

sum(TOTAL {<Year={$(=only(Year)-1)}>}Aggr(sum({<Year={$(=only(Year)-1)}>} Numerator)*only({<Year={$(=only(Year)-1)}>} Weight), MeasureID))

/

Sum(TOTAL {<Year={$(=only(Year)-1)}>}Aggr(Max({<Year={$(=only(Year)-1)}>}Weight) , MeasureID))

,0.1)

View solution in original post

2 Replies
Highlighted
MVP
MVP

may be

round(

sum(Aggr(sum({<Year = {'$(=Max(Year))'}>}Numerator)*only({<Year = {'$(=Max(Year))'}>}Weight), MeasureID,Year))

/

Sum(Aggr(Max({<Year = {'$(=Max(Year))'}>}Weight) , MeasureID,Year))

,0.1)

and

round(

sum(Aggr(sum({<Year = {'$(=Max(Year)-1)'}>}Numerator)*only({<Year = {'$(=Max(Year)-1)'}>}Weight), MeasureID,Year))

/

Sum(Aggr(Max({<Year = {'$(=Max(Year)-1)'}>}Weight) , MeasureID,Year))

,0.1)

Highlighted

Hi Fion, you can try the last year expression as:

round(

sum(TOTAL {<Year={$(=only(Year)-1)}>}Aggr(sum({<Year={$(=only(Year)-1)}>} Numerator)*only({<Year={$(=only(Year)-1)}>} Weight), MeasureID))

/

Sum(TOTAL {<Year={$(=only(Year)-1)}>}Aggr(Max({<Year={$(=only(Year)-1)}>}Weight) , MeasureID))

,0.1)

View solution in original post