Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Last occurrence set analysis

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?

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

8 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

You could try something like this:

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

- Marcus

Not applicable
Author

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

tranNametranType
1Apples3
2Apples3
3Apples1
4Apples3
5Apples1

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 !!

sunny_talwar

May be this

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

View solution in original post

mangalsk
Creator III
Creator III

Hello,


Try this if works else share data and exact expected result


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

marcus_sommer
MVP & Luminary
MVP & Luminary

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

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

- Marcus

Not applicable
Author

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

Not applicable
Author

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.

08748190
Contributor II
Contributor II

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