topic Re: Last occurrence set analysis in QlikView

Last occurrence set analysis

Sat, 06 May 2017 18:01:55 GMT

My transactions have a [transaction type].

I want to count how many transactions each customer has had since their last [transaction type] = 3.

I'm quite lost, to be fair.

Breaking out down, I can get the last "3" transaction thus:

max({< [transaction type]={3} >} [order time])

But how to then say "count everything after the order date from this expression". ??

I've been playing all day with aggr and other functions, but think I'm just getting into a bigger mess.

Any help?

Re: Last occurrence set analysis

marcus_sommer — Sun, 07 May 2017 07:52:19 GMT

You could try something like this:

count({< [transaction] = {"=[order time]>max({< [transaction type]={3} >} [order time])"}>} [transaction])

- Marcus

Re: Last occurrence set analysis

Sun, 07 May 2017 17:20:19 GMT

Thanks Marcus, your code makes logical sense, but I'm still struggling.

I decided to simplify my data, to try and see clearer.
I'm now identifying each row with an incremental id (effectively encompassing id and time into 1 field)

Tran	tranName	tranType
1	Apples	3
2	Apples	3
3	Apples	1
4	Apples	3
5	Apples	1

So my new expression (not working) is ...
Expression = count( {<[Tran] = { "> max( {<[tranType]={3}>} [Tran] )" } >} [Tran] )

Dimensions used = [tranName]

My result is just '0'.

I did a second expression with just max( {<[tranType]={3}>} [Tran] ), and that gave me '4'.

I'm not sure if I am getting syntax, or the whole concept wrong !!

Re: Last occurrence set analysis

sunny_talwar — Sun, 07 May 2017 22:27:29 GMT

May be this

Sum(Aggr(If([order time] > Max(TOTAL <Customer>{<[transaction type] = {3}>} [order time]), 1, 0), customer, [order time]))

Re: Last occurrence set analysis

mangalsk — Mon, 08 May 2017 05:10:32 GMT

Hello,

Try this if works else share data and exact expected result

count({<[order time]={">=max({< [transaction type]={3} >} [order time])"}>}[transaction ID])

Re: Last occurrence set analysis

marcus_sommer — Mon, 08 May 2017 07:53:55 GMT

Try it with changing the > to >= to see if it made a difference:

count( {<[Tran] = { ">=max( {<[tranType]={3}>} [Tran] )" } >} [Tran] )

- Marcus

Re: Last occurrence set analysis

Mon, 08 May 2017 07:56:06 GMT

Thanks so much Sunny, seems to do the job nicely !

I'll sit down and make sure I understand It properly, add to my learning; but for now, it's doing what it should

Re: Last occurrence set analysis

Mon, 08 May 2017 07:58:21 GMT

Mangal,

Thanks for your efforts, 'unfortunately' Sunny's suggestion 'just worked'.

Maybe some nuance of my data ?

Appreciate your suggestions, and as a result, I will at least go learn a bit more about those nested statements.

Re: Last occurrence set analysis

08748190 — Thu, 20 Jun 2019 15:40:16 GMT

Hello ,

Can you give me an example for the last occurence without user for the function max

for example :

This salary work for 100 % full time and after 60%

so i want to get of 60%,

Set analysis :

( sum ( {$<

[__FTE]={">= max({<__Period_id = {'$(vdate_analyse)'}>}[__FTE])"},

[__Contract_Type]-={'IN','PS','ST','TV'}

>} distinct [__FTE]) )

With :

FTE : full time percentage (100.00 or 60.00,...)

Thank you in advance