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.

       

       

      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.