Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Exclusion Problem - Finding previous Tier

Hi Guys,

I have an interesting set analysis exclusion problem with which I am struggling right now. Can someone please help ?

Problem Statement:

DateId = 0 always gives the current tier for a given payer. For instance: In the attached prototype,  Payer 1 has T4/PB/PA as the current tier. In this problem, I would like to find out what the previous tier was for each payer. In determining the previous tier, following conditions must be met:

1. DateId must not be 0 and the search should be conducted in increasing order of DateIds (1, 2, 3 etc.)

2. Previous Tier must not be the same as current tier.

The expression that I am using to get the Previous Tier is:

FirstSortedValue({<Tier = E({<DateId={'0'}>})>} Distinct Tier, DateId)

Please see the attached Qlikview document for a prototype.

In the prototype -

What's Working ?

Payer 2 has current tier T4 in dateid 0. Also, the same tier exists in dateid 1. Therefore, the logic for previous tier ignores T4 and jumps to T4/NPG/SE, which is correct.

  

Whats' not working ?

Payer1 has current tier T4/PB/PA in dateid 0. For DateId =1 , tier is T4, which should have been picked up as the previous tier. However, it jumps to DateId = 2 and shows T3 as the previous tier.

Note:

As soon as Payer 1 is selected, the previous tier is correctly picked up as T4. I am unable to understand why this happens.


Thanks,

Puneet.

1 Reply
lironbaram
Partner - Master III
Partner - Master III

hi set analysis won't work in this case

because what happen the exlude function is exluding all tier types that are in tier 0

in your example T4,T2,T4/PB/BAon   they are exluded for all payers

this is the reason when you look at all payers , it doesn't work and if you look only on payer 1 it working

i would think the best way if possible is doing it in the script