Count values that are the same in previous date

[danieloberbilli]

Dear All,

I got non-distinct IDs which can occur on several dates. I would like to identify which IDs are the same than in the date before - preferably on the dashboard to keep it most dynamic.

Having the ID and Date (as numeric field) I thought to solve it like that - but without success:

count({<ID=p({<DateNum={'$(=DateNum-1)'}>} ID)>}  ID)

PFA the .qvw

Thanks in advance for your support.

Kind Regards

Daniel

[Anonymous]

Are the dates in you InLine load supposed to be the:

1st of Jan, Feb & Mar

or the :

1st, 2nd & 3rd of Jan ?

[Gysbert_Wassenaar · Accepted solution]

You can't use set analysis for this. The set is calculated at the chart level, not the row level. Perhaps creating a flag field will work for you. See attached qvw.

[marcus_sommer]

Hi Daniel,

I think your approach couldn't work because you creates an adhoc-variable which will have only one global value for the whole table - calculated once before the table-calculation and used for each row.

If the object is quite static you could it rather pre-calculate within the script. If not you could maybe use an approach like this:

concat({<ID=p(ID)>}  ID, ',') & ' - ' & above(concat({<ID=p(ID)>}  ID, ','))

But I don't know a practically way to exclude the ID's from each string-side which are not into the other or something similar and in the end count the number of ','. If you don't have really many ID's per day you could match these string-parts in a if-loop maybe with match() & subfield() but it would be not a really nice solution.

Maybe another could give some ideas to match such strings in a native qv gui-object - per scripting with real loops it's easier (is the using only with fat-client or plugin an user-defined macro-function might be a thought worth).

- Marcus

[danieloberbilli]

Thank you so far.

If I solve it in the script e.g. with Gysberts approach, how can I also identify and count 'new' and  'left/no more available' beside of the count of 'same'?

[danieloberbilli]

Hi Bill,

Its the first of January, the first of Feb and so on

Thanks for your help

Kind Regards

Daniel

[danieloberbilli]

At the moment I can't think of any other solution than creating a table for each month and joining them with the previous month - then I can simply flag which IDs are new, gone or the same. I also could do the count/sums in the script in an additional table. So at the end I guess I have to loop all these joins and sum-tables? Or is there a better way to solve this?

[Gysbert_Wassenaar]

That's a fine solution. It's a simple variant of the AsOf table concept. You can use that in more advanced scenarios too as described in this document: Calculating rolling n-period totals, averages or other aggregations