Nested aggregation error for counting dates

[venky77777]

In qliksense kpi getting error at the time counting dates inside if condition. The expression is

Count(if(((if(IsNull(date(addmonths(monthstart(max(Sales_Date)),0),'YYYY-MM-DD')),
max(Sales_Date), Sales_Date))
-
{<Month, Year>}MAX_INV_DATE) > 90, {<Month, Year>}MAX_INV_DATE))

Without count I'm getting dates in table, but i want to show count in kpi. Can any one help me. 

[lennart_mo]

Hi @venky77777,

This error occurs, when you try to use an aggregation-function inside of another aggregation. In this case your aggregations are count() and max(). You can solve this by either using the TOTAL qualifier in the max()-functions or by using the AGGR()-function.

Which one may be more appliable depends on the use-case.

[venky77777]

Hi @lennart_mo , 

Thank for reply, I tried like below, but getting 0 in kpi and in table as well, 

Count(aggr(if(((if(IsNull(date(addmonths(monthstart(max(Sales_Date)),0),'YYYY-MM-DD')),
max(Sales_Date), Sales_Date))
-
{<Month, Year>}MAX_INV_DATE) > 90, {<Month, Year>}MAX_INV_DATE),

{<Month, Year>}MAX_INV_DATE))

Can you correct it, if any mistake? 

[lennart_mo]

Which dimension were you using initially in your table, where you got the correct data?

Maybe try using that instead of {<Month, Year>}MAX_INV_DATE) as the Dimension for the AGGR()

[venky77777]

• Hi @lennart_mo 

I used dimension cust_id, the expression I kept is below

Count(aggr(if(((if(IsNull(date(addmonths(monthstart(max(Sales_Date)),0),'YYYY-MM-DD')),
max(Sales_Date), Sales_Date))
-
{<Month, Year>}MAX_INV_DATE) > 90, {<Month, Year>}MAX_INV_DATE),

Cust_Id))

Now also values getting 0. Any suggestion or correction. 

[lennart_mo]

It's difficult to tell since i don't know what your data model looks like.

Could you maybe provide a screenshot of the data model as well as your initial table, in which you got values? Maybe that will help us find a solution!

[venky77777]

Hi @lennart_mo 

Thanks for your help, finally I got solution when I exclude month & year fields inside count() and adding total inside max() as below

Count({<Month, Year>} aggr(if(((if(IsNull(date(addmonths(monthstart(max(total Sales_Date)),0),'YYYY-MM-DD')),
max(toral Sales_Date), Sales_Date))
-
{<Month, Year>}MAX_INV_DATE) > 90, {<Month, Year>}MAX_INV_DATE),

Cust_Id)).

Now getting excited without nested aggregation error with result.