Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

If Statement set analysis in count distinct measure

Hey Experts,

I am trying to create a bar chart visual using the AsOf Table principle to show accumulation of the current series status over time. Attached is the representative data set produced by the associated link between my fact table and the AsOf table. This is loaded correctly and matches the desired output of all possible combinations. Using the [Pkg Submitted AsOfMonth] as my only Dimension, I'd like to make the following two bars:

Measure #1: Create a "Total Package Series" bar that counts the distinct [Package Series] in each month. This was very simple to produce.

Count(DISTINCT [Package Series])

 

Measure #2: Create a "Phase 1" bar that counts distinct [Package Series] if the aggregate max [Package Phase Tracking ID] grouped by [Package Series], [Pkg Submitted AsOfMonth] equated to a [Package Phase ID] = 1419

You can see my failed attempts below:

 

If([Package Phase Tracking ID]= aggr(nodistinct max([Package Phase Tracking ID]),[Package Series],[Pkg Submitted AsOfMonth]),[Package Phase ID])

Attempt #1
Count(DISTINCT {<[Package Phase ID] = {'1419'}>}[Package Series])

Attempt #2
Count(DISTINCT {<[$(=If([Package Phase Tracking ID]= aggr(nodistinct max([Package Phase Tracking ID]),[Package Series],[Pkg Submitted AsOfMonth]),[Package Phase ID]))] = {'1419'}>}[Package Series])

 

1 Reply
Highlighted
MVP
MVP

Can you share a sample data with expected output?