topic Aggregate Function Issue in QlikView

Aggregate Function Issue

Shiva123 — Thu, 18 Mar 2021 00:23:46 GMT

All i have a question , i am currently have an expression which is doing a aggregate function over a variable and after that i am doing a calculation . I also have a cyclic group in the chart ( pivot table ) . The problem is aggregate function works for a certain selections and for certain selections it doesn't work (my thinking is because the aggregated dimension has multiple values) and the aggr function cannot understand . Is there any way to solve this

expression

if((aggr($(varibale1 ), dimension1, dimension 2, etc ))=0 or isnull((aggr($(variable 1), dimension1, dimension 2 , etc))),

(SUM({$<YYYY= {'2020'},>} $(variable2 )))*(avg({$<YYYY= {2020}=>} field ))

,$(varibale1 ))

here the dimension in aggr function are same and dimenison2 falls into 2 categories of dimension 1 , so its failing to calculate the logic .

any help would be appreciated

Re: Aggregate Function Issue

alex00321 — Thu, 18 Mar 2021 08:26:27 GMT

Hi, if you could provide some sample and the expected result, that would be helpful for others to help you. Thanks!

Re: Aggregate Function Issue

Shiva123 — Thu, 18 Mar 2021 15:11:21 GMT

@alex00321 Attached is the image , the cyclic group and the dimension 2 values are added in the aggr . If you see the last two lines they are summing up but the first line is not summing up. The expression used for first line

(SUM({$<YYYY= {'2020'},>} $(variable2 )))*(avg({$<YYYY= {2020}=>} field )) because there is no new data for current year and its using previous year data and multiplying with a field .