topic Re: Count values that are the same in previous date in QlikView

Count values that are the same in previous date

danieloberbilli — Wed, 25 Feb 2015 11:31:24 GMT

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

Re: Count values that are the same in previous date

Anonymous — Wed, 25 Feb 2015 11:56:23 GMT

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

1st of Jan, Feb & Mar

or the :

1st, 2nd & 3rd of Jan ?

Re: Count values that are the same in previous date

Gysbert_Wassenaar — Wed, 25 Feb 2015 12:36:38 GMT

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.

Re: Count values that are the same in previous date

marcus_sommer — Wed, 25 Feb 2015 12:48:22 GMT

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

Re: Count values that are the same in previous date

danieloberbilli — Wed, 25 Feb 2015 23:38:35 GMT

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'?

Re: Count values that are the same in previous date

danieloberbilli — Wed, 25 Feb 2015 23:40:46 GMT

Hi Bill,

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

Thanks for your help

Kind Regards

Daniel

Re: Count values that are the same in previous date

danieloberbilli — Thu, 26 Feb 2015 01:27:17 GMT

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?

Re: Count values that are the same in previous date

Gysbert_Wassenaar — Thu, 26 Feb 2015 06:44:16 GMT

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