Break the association in concat

[OmarBenSalem]

Hi all ( stalwar1‌ )

In my source, there are dates where sum(Net) and Sum(Gross) are both missing =0; so I want to show the dates where these measures are =0

here's what I'm doing:

if(count({<AsOf_Date={"=sum({1}Current_Notional_Gross)=0"}>}AsOf_Date)=0 and count({<AsOf_Date={"=sum({1}Current_Notional_Net)=0"}>}AsOf_Date)=0,'No missing Dates for aggregated Notionals',

'Missing Dates for Aggregated Notionals (No Data) : '&Concat(aggr(if(sum({1}Current_Notional_Gross)=0 and sum({1}Current_Notional_Net)=0,aggr(only({1}AsOf_Date),AsOf_Date),Null()),AsOf_Date),' ,'))

It shows the correct result:

But when I make any selection, it does not show a thing, although I've tried to "break the association" by using aggr(only({1}AsOf_Date),AsOf_Date) instead of AsOf_Date..

Can u see what I'm missing to always be able to show these dates no matter the selection I make?

[sunny_talwar]

May be try this

Concat({1} Aggr(If(Sum({1} Current_Notional_Gross) = 0 and sum({1} Current_Notional_Net) = 0, AsOf_Date), AsOf_Date), ' ,'))

[OmarBenSalem]

Already tried it..

result:

Qlik knows there are missing dates by does not show a thing:

[sunny_talwar · Accepted solution]

My bad, try this

Concat({1} Aggr(If(Sum({1} Current_Notional_Gross) = 0 and sum({1} Current_Notional_Net) = 0, Only({1} AsOf_Date)), AsOf_Date), ' ,'))

[OmarBenSalem]

Hey synny,

This worked !

But, I have a question, why is this working while this did not?

Concat({1}aggr(if(sum({1}Current_Notional_Gross)=0 and sum({1}Current_Notional_Net)=0,aggr(only({1}AsOf_Date),AsOf_Date),Null()),AsOf_Date),' ,'))

[sunny_talwar]

Because there is an invisible aggregation outside of Aggr() which is not ignoring selections

There is an invisible Only() function, which is not ignoring selections