Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Joseph_Kipling
New Contributor

Count Set analysis for Equal fields from different tables - does that work?

Hi, 

I'm trying to perform set analysis to count something by having two fields equal each other. These two field are from different data tables - does that work? 

The fields [Leads.LeadCode] and [Leaflet Design] are in different tables but a linked together by a common field. 

count({1<[Leads.LeadType]={'Retail'}, [Leads.LeadCode]=[Leaflet Design], [Fixed_Dates.Date.autoCalendar.WeeksAgo]={"<=$(=$(vLastWeek)+12)>$(=$(vLastWeek)-1)"}, [Leaflet Design]={'RM155','HD211','LD106','LD215'}> distinct [Leads.LeadID]&[Leads.First]&[Leads.Last])

any help?

7 Replies
pradosh_thakur
Honored Contributor II

Re: Count Set analysis for Equal fields from different tables - does that work?

if([Leads.LeadCode]=[Leaflet Design],
count({1<[Leads.LeadType]={'Retail'}, [Fixed_Dates.Date.autoCalendar.WeeksAgo]={"<=$(=$(vLastWeek)+12)>$(=$(vLastWeek)-1)"}, [Leaflet Design]={'RM155','HD211','LD106','LD215'}> distinct [Leads.LeadID]&[Leads.First]&[Leads.Last]))

and if you have any unique field and all the rows concerned are uniquely defined

count({1<[Leads.LeadType]={'Retail'}, unique_field={"=[Leads.LeadCode]=[Leaflet Design]"}, [Fixed_Dates.Date.autoCalendar.WeeksAgo]={"<=$(=$(vLastWeek)+12)>$(=$(vLastWeek)-1)"}, [Leaflet Design]={'RM155','HD211','LD106','LD215'}> distinct [Leads.LeadID]&[Leads.First]&[Leads.Last])
Learning never stops.
Highlighted
Joseph_Kipling
New Contributor

Re: Count Set analysis for Equal fields from different tables - does that work?

OK thanks, my concern was the the if statement was then not within the count function and i wanted to have a function that ignores filters. Can you do this with an if statement?

pradosh_thakur
Honored Contributor II

Re: Count Set analysis for Equal fields from different tables - does that work?

Did the above work ? I didn't get what you are asking for ?
Learning never stops.
Joseph_Kipling
New Contributor

Re: Count Set analysis for Equal fields from different tables - does that work?

Yes that worked - Thanks. I actually had this solution previously but the aim was for the calculation to ignore all selections. At the moment, when i select a filter the chart updates (I would like it not to change). 

pradosh_thakur
Honored Contributor II

Re: Count Set analysis for Equal fields from different tables - does that work?

Ohk. Even when you use 1 ? Anything else you need or you got the answer?
Learning never stops.
Joseph_Kipling
New Contributor

Re: Count Set analysis for Equal fields from different tables - does that work?

Yes even when I use 1.. weird

Re: Count Set analysis for Equal fields from different tables - does that work?

You should be able to ignore selections even with an if statement

If(Only({1} [Leads.LeadCode]) = Only({1} [Leaflet Design]),

But if it is inside your Count function, you might not need it.... If you can share the exact expression which worked without any selection, we might be able to help you point out any changes that you might need in it to make it work with selections (everything ignored)