topic Re: Equivalent to PowerPivot's EARLIER function in Qlik in QlikView

Equivalent to PowerPivot's EARLIER function in Qlik

Tue, 17 Feb 2015 18:08:55 GMT

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

Re: Equivalent to PowerPivot's EARLIER function in Qlik

marcus_sommer — Wed, 18 Feb 2015 19:44:55 GMT

I think you could do it with calculated dimensions and/or set analysis expressions quite similar, have a look here:

Calculated Dimensions

AGGR...

A Primer on Set Analysis

- Marcus

Re: Equivalent to PowerPivot's EARLIER function in Qlik

Wed, 04 Mar 2015 16:57:56 GMT

Hi Marcus

unfortunately this seems a the wrong way round.

I found an example of how to use the aggr function so you could do calculations like rank the sales of each sales person. So this is creating a collection of sets and then ranking them.

What i need to do are rank within the sets. I looked everywhere and can't find any way of achieving this. Seems like quite an obvious thing to do which is why Powerpivot has this EARLIER function. I use this so much that without seeing a clear path to replacing it I'll have abandon Qlik. Maybe someone else has an idea

Mike

Re: Equivalent to PowerPivot's EARLIER function in Qlik

luciancotea — Wed, 04 Mar 2015 17:07:29 GMT

See FirstSortedValue()

Re: Equivalent to PowerPivot's EARLIER function in Qlik

marcus_sommer — Wed, 04 Mar 2015 17:14:08 GMT

I'm sure that are ways within qv to reach your goals. If I think again about your question you want to compare a row-value with the row-value from another record, yes? Then you could use previous() or peek() within the script (it's rather recommended to do such steps within the script or at least generate some pre-calculations) or you used functions like above() or below() within a pivot-chart.

Peek() vs Previous() – When to Use Each

- Marcus

Re: Equivalent to PowerPivot's EARLIER function in Qlik

Wed, 04 Mar 2015 17:19:08 GMT

Unfortunately in this case I need 1,2,11,12,21,22 so first doesn't help, but might in other examples

Thnaks

Re: Equivalent to PowerPivot's EARLIER function in Qlik

Wed, 04 Mar 2015 17:21:14 GMT

Sounds like I need someone who know what they are doing!!! Essentially yes you are calculating based off previous row, but only within sets. Qlik have pointed me at a partner who seems keen to have a go!!!

Thanks

Re: Equivalent to PowerPivot's EARLIER function in Qlik

Mon, 09 Mar 2015 17:33:33 GMT

Well turns out the answer is

=aggr(rank(min({<Transaction={'redeem'},[Transaction ID]=>}-[Transaction ID])),[Product ID],[Gym ID],[Transaction ID])

Generated by a qlik partner, but I have no idea why the syntax works!!!