topic Set Analysis with Identity of 1 is Responding to Filters in App Development

Set Analysis with Identity of 1 is Responding to Filters

walkasia — Sat, 16 Nov 2024 17:31:49 GMT

I have a KPI that is attempting to compute the number of our donors who contributed in both 2018 and 2019. I do not want these calculation to respond to filters. I am using the Identity value of 1...

=count(distinct

if(Aggr(sum({1<[Contribution Receive Date.autoCalendar.Year]={'2019'}, [Is Deductible]={'YES'}>}[Contribution Total Amount]),[Rollup ID])>0 AND

Aggr(sum({1<[Contribution Receive Date.autoCalendar.Year]={'2018'}, [Is Deductible]={'YES'}>}[Contribution Total Amount]),[Rollup ID])>0,

[Rollup ID]))

Is what I am attempting to do even possible?

Much thanks!

- Gwen Harter

Re: Set Analysis with Identity of 1 is Responding to Filters

skamath1 — Wed, 03 Feb 2021 16:35:06 GMT

I think you need to add the set analysis to the aggr function also..

See the help for syntax

https://help.qlik.com/en-US/sense/November2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm

Re: Set Analysis with Identity of 1 is Responding to Filters

Ksrinivasan — Wed, 03 Feb 2021 18:16:37 GMT

hi,

try this

Count({$<[Contribution Receive Date.autoCalendar.Year]={'2018','2019'}, [Is Deductible]={'YES'}>} [Rollup ID])

ksrinivasan

Re: Set Analysis with Identity of 1 is Responding to Filters

walkasia — Wed, 03 Feb 2021 19:33:53 GMT

That is interesting! I am still new to all of this and did not know that the set analysis could be applied to the aggr function. It did not solve my problem in this case, but is still good for me to learn. Thanks!

Re: Set Analysis with Identity of 1 is Responding to Filters

walkasia — Wed, 03 Feb 2021 19:37:52 GMT

That wasn't quite where I needed to be, but was a step in the right direction, as filtering by Receive Date is a lot more logical than summing up the contribution amounts. But as noted initially, I do NOT want the KPI to respond to filters, so need an identity of 1 rather than $.

By drawing some Venn diagrams and thinking things though carefully, I applied the Receive Date approach and came up with the below formula, which (YAY!) does not respond to filters.

// all of the people who contributed in EITHER year

Count({1<[Contribution Receive Date.autoCalendar.Year]={'2018','2019'}, [Is Deductible]={'YES'}>} distinct [Rollup ID])

// minus all of the people who contributed ONLY in 2018

-(Count({1<[Contribution Receive Date.autoCalendar.Year]={'2018','2019'}, [Is Deductible]={'YES'}>} distinct [Rollup ID]) -
Count({1<[Contribution Receive Date.autoCalendar.Year]={'2019'}, [Is Deductible]={'YES'}>} distinct [Rollup ID]))

// minus all of the people who contributed ONLY in 2019

-(Count({1<[Contribution Receive Date.autoCalendar.Year]={'2018','2019'}, [Is Deductible]={'YES'}>} distinct [Rollup ID]) -
Count({1<[Contribution Receive Date.autoCalendar.Year]={'2018'}, [Is Deductible]={'YES'}>} distinct [Rollup ID]))