Set Analysis with Identity of 1 is Responding to Filters

[walkasia]

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

[skamath1]

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

[Ksrinivasan · Accepted solution]

hi,

try this

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

ksrinivasan

[walkasia]

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!

[walkasia · Accepted solution]

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