Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DMG
Contributor III
Contributor III

Counting with a two part IF

Hi i have two sets of data joined by a key, for example

Service Name

A
B
C

Component , Type, Service Name, 

1, Legacy, A

2, Legacy, A

3, Strategic, A

4, Legacy, B

5, Legacy, C

 

In a synthetic measure called "Classification", i'm using the following formula to create this 

if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),'Hybrid',if(Count({<Type={'Strategic'}>}distinct Component)>0,'Strategic Only',
if(Count({<Type={'Legacy'}>}distinct Component)>0,'Legacy Only','Neither')))

The idea is that a table will have a single line for each Service Name, and then the Classification field will indicate

Hybrid if there are entries in Type mapped to Service Name, for both Legacy and Strategic, or Strategic Only if there is only Strategic entries, etc. (if theres a better way to do the above feel free to suggest but that isnt the question)

This all works as above for the formula to give me a measure containing, Hybrid, Strategic Only, etc, but i would also like a kpi box to show the total of services where the Strategic and Legacy exist, i.e. where they are hybrid, i thought i might be able to do something like this

if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),Count(distinct Service Name))

But that doesnt appear to calculate the correct value.

Any ideas ?

 

Also is there a way to make the synthetic measure i've created (Classification) a field users can filter on ?

1 Solution

Accepted Solutions
stevejoyce
Specialist II
Specialist II

The only problem with @brunobertels was wrong field names, but I agree that should work.  and your filterable calculated dimension wrap your expression around with an aggr and aggregate over fields you want to be selected when you use it as a filter.

 

Filter:

=aggr(

if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),'Hybrid'
,if(Count({<Type={'Strategic'}>}distinct Component)>0,'Strategic Only'
,if(Count({<Type={'Legacy'}>}distinct Component)>0,'Legacy Only','Neither')))


,[Service Name])

 

Hybrid - KPI

sum(aggr(

if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0), Count(distinct [Service Name]))

,[Service Name])

)

View solution in original post

4 Replies
brunobertels
Master
Master

Hi 

 

May be aggregate your expression by Service Name : 

 

aggr(

if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),Count(distinct Service Name))

,ServiceName)

 

and you can add your synthetic mesure in a filter pane object to get a list of value , user can select. 

DMG
Contributor III
Contributor III
Author

Thanks but that just returns " - " as the result ?

stevejoyce
Specialist II
Specialist II

The only problem with @brunobertels was wrong field names, but I agree that should work.  and your filterable calculated dimension wrap your expression around with an aggr and aggregate over fields you want to be selected when you use it as a filter.

 

Filter:

=aggr(

if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),'Hybrid'
,if(Count({<Type={'Strategic'}>}distinct Component)>0,'Strategic Only'
,if(Count({<Type={'Legacy'}>}distinct Component)>0,'Legacy Only','Neither')))


,[Service Name])

 

Hybrid - KPI

sum(aggr(

if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0), Count(distinct [Service Name]))

,[Service Name])

)

DMG
Contributor III
Contributor III
Author

Perfect thanks