Set Analysis to compare today to the past

[#1]

Hi,

I have this table structure :

ID     DATE      NUMBER

1      1           1         

2      1           1    

3      1           1   

1      2           1    

2      2           2    

3      2           1    

I want to count the distinct ID, which have DATE = $(v_date), and a NUMBER greater than the NUMBER for the all the previous DATE for the same ID.

(in my example with $(v_date)=2 the answer is 1 : the NUMBER for ID 1 and 3 did not increase, and the NUMBER for ID 2 increased before DATE=2)

I think I have to use set analysis, but I forget how to do those kind of sets.

Thank you for your help !

Yannick

[Gysbert_Wassenaar]

I don't think this can be done completely with only set analysis. Try this expression:

=count({<DATE={$(v_date)}>}DISTINCT if(NUMBER>aggr(max(NUMBER,2),ID), ID))

[#3]

Hi Gysbert,

Thank you very much for your help !!

What you are proposing is working for me

It is more simple that what I was trying to do :

I was trying to check if NUMBER is different on D DAY and on D-1 Day

But since NUMBER is only suppose to grow in time, your proposition is OK for me.

Just because I'm curious, how would you do it if I wanted to check that NUMBER for $(v_date) is > to NUMBER for $(v_date_1) ?