1 Reply Latest reply: May 19, 2013 4:59 AM by Liron Baram RSS

    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.




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