7 Replies Latest reply: Mar 9, 2015 1:33 PM by Mike Asplin RSS

    Equivalent to PowerPivot's EARLIER function in Qlik

      Hi Newbie question

       

      I'm trying to understand any limitations of Qlik calculated fields. I am moving over form PowerPivot and the support guys are stuck on creating the equivalent of the EARLIER function. I was wondering if anyone had experience moving from PowerPivot and can tell me if there is an equivalent or not. The EARLIER function allows you create filters/sets on the fly of things that match within the database. i use this a lot for ranking transactions like finding the first transaction for each customer.

       

      The particular example i'm looking for is as follows in powerpivot

       

      =if([Transaction]="redeem" && [Pass Group]<>"lesson",

           RANKX(

                         FILTER(

                                      transactions,[Transaction]="redeem" &&

                                      [Parent Product ID]=EARLIER([Parent Product ID]) &&

                                      [Gym ID]=EARLIER([Gym ID]) &&

                                      [Transaction Month]=EARLIER([Transaction Month])

                                      ),

                          [Transaction No],,1,skip

                          ),

            blank()

            )

       

         So what this is doing is for each line in the transaction table it finds the other lines that are redeem, same gym id, same month and same product ID, then ranks the line it is on amongst these lines based on transaction no.  It then moves ot he next line and creates a new set on the fly etc etc processing each line in the transaction table.  At the end I have a whole raft of transctions marked as 1 as they were the first transaction of a month for a gym for a product.

       

           It may be this is just beyond Qlik or much easier to do within PowerPivot so any guidence appreciated.

       

      Mike