Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to create a suitable expression using the Qlik Sense KPI extension that returns a distinct count of Members who appear in both Divisions (there are currently only 2).
An extract of my 5,000 row dataset looks like this:
Division | Area | Member |
Region East | Grafton | George |
Region East | Newcastle | Jack |
Region East | Byron Bay | Joe |
Region West | Bullamanka | Katrina |
Region West | Broken Hill | Joe |
Region West | Parkes | Bluey |
Region West | Albury | Beachball |
Region West | Wentworth | Costello |
Region West | Wilcannia | Abbott |
Region West | Dubbo | Jack |
Region West | Goulburn | Amos |
Region West | Cobar | Joe |
Looking at this dataset, based on my requirements, the expression should return the correct answer of 2 (Jack and Joe - as they at least appear once in both Region East and Region West)
I'm stuck at the moment and started with this expression when maybe I should use set analysis:
aggr (count(Member), Division, Member ) )
Any help will be greatly appreciated.
Thanks.
Hi Tresesco,
I'm using the expression editor in Qlik Sense desktop 3.1 SR3
i'll give it another try.
thanks again,
OZzie
Hi Marcus,
I agree with you. The standard KPI extension only allows a measure...
What I've decided to do is trap those entries during the ETL process through a derived field and use the following expression to display the result.
Count(DISTINCT {$<Division= ,[Multi-Directorate Members]={"Yes"}>} [Member])
As you can see, the above is derived from suggestions from you and our colleagues responding to my question.
Thank you for your assistance here.
Ozzie
>>thank you for your reply. Unfortunately it returns the total number of unique members across the whole dataset (623)
The expression should read:
=Sum(Aggr(If(Count(DISTINCT Division & Region) >= 2, 1, 0), Member))
(sum, not count - count will return everything, but sum should work)
Hi Jonathan,
A small change to your formula and it works!!
Sum(Aggr(If(Count(DISTINCT Division & Member) >= 2, 1, 0), Member)).
This removes the need to do any additional ETL scripting to tag the member records in both divisions.
Thanks to all who took time out to help me.
Hi Bunchong,
Thank you for your time to respond here. The formula that works for me is in my response to Jonathan below.
Kind regards,
Ozzie