Set Analysis Exclusion Problem - Finding previous Tier

[#1]

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.

[lironbaram]

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