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

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

Joseph_Kipling — Sat, 16 Nov 2024 21:29:17 GMT

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?

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

pradosh_thakur — Fri, 01 Feb 2019 11:03:26 GMT

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])

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

Joseph_Kipling — Fri, 01 Feb 2019 11:10:47 GMT

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?

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

pradosh_thakur — Fri, 01 Feb 2019 11:13:32 GMT

Did the above work ? I didn't get what you are asking for ?

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

Joseph_Kipling — Fri, 01 Feb 2019 11:21:43 GMT

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).

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

pradosh_thakur — Fri, 01 Feb 2019 11:26:25 GMT

Ohk. Even when you use 1 ? Anything else you need or you got the answer?

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

Joseph_Kipling — Fri, 01 Feb 2019 12:14:31 GMT

Yes even when I use 1.. weird

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

sunny_talwar — Fri, 01 Feb 2019 12:42:15 GMT

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)