Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Break the association in concat

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:

Capture.PNG

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?

1 Solution

Accepted Solutions
sunny_talwar

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), ' ,'))

View solution in original post

5 Replies
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
Author

Already tried it..

result:

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

Capture.PNG

sunny_talwar

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
Author

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

Capture.PNG

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