turn a measure into a dimension

[KWHITEHURST]

Hoping some can please help

I have an example table below, where i have determined which policies are still live, however, i want to be able to filter just on those where the PIF column = Live, can any please assist as to how to do this, i've tried the Aggr function, but to no avail

POL NO            MAX OF POL VERSION                PIF COUNT        PIF

KKK2222                         4     

KKK3333                          5                                                    1                 LIVE

KKK4444                         8                                                 

KKK555                            1                                                     1                LIVE

FORMULAS ARE:

MAX OF POL VERSION    =TEXT(Aggr(Max([PET_TRANSACTIONS.POLICYVERSION]),[PET_TRANSACTIONS.POLICYNUMBER]))

PIF COUNT =   IF([PET_TRANSACTIONS.POLICYSTATUS]='Live', count(distinct [PET_TRANSACTIONS.POLICYNUMBER]))

PIF =      if(IF([PET_TRANSACTIONS.POLICYSTATUS]='Live', count(distinct[PET_TRANSACTIONS.PURCHASEMONTH]))>=1,'Live','')                          

[MartW]

Why Don't you create a filter object and put the dimensional value PET_TRANSACTIONS.POLICYSTATUS in this.

then you can filter on LIVE and the entire Dashboard including the table will update

also what you can do is to create the calculated PIF field in the load script and then add this to the filter object.

the benefit of this is that Qlik will not slow down when in exploring mode on the sheets of an app. (because the outcome is already calculated when reloading the app.)

[KWHITEHURST]

thanks for your suggestion, however if i use the POLICYSTATUS as a filter it will not work i.e. my data actually has the likes of below, therefore filtering for LIVE would also include the policy below

POL NO            MAX OF POL VERSION                POLICY STATUS

KKK2222                         4                                             CANCELLED

KKK2222                          3                                            LI VE

[MartW]

Then If possible create a new field in the loadscript where you include the if statement for PIF.

I think you need to modify this slightly (from how I look at this) but then you can add filter for the new field and use this.