topic Re: Count based on time stamp in QlikView

Count based on time stamp

fatihsahin — Thu, 17 Sep 2015 14:08:56 GMT

Hi Community,

lets say I have following structure

ID Date Status

1 10.10.2014 ok

2 15.10.2014 ok

3 20.10.2014 ok

1 15.08.2015 not ok

2 20.08.2015 not ok

Not let say I want to count only the Status of an ID but only with the last time stamp.

So the result of the count should be for example for ID 1 and Status OK = 0 and for ID 1 and Status "not ok" = 1.

Basically just count the last valid status and ignore the previous status.

Hope you can help me with that.

Regards

Fatih

Re: Count based on time stamp

sinanozdemir — Thu, 17 Sep 2015 14:40:37 GMT

Hi Fatih,

Here is one way to achieve this in a straight table:

I used RowNo() and aggregated over ID and Date dimensions, and based on the ranking I gave 'ok' to 1's and 'not ok' to the rest.

Hope this helps.

Re: Count based on time stamp

fatihsahin — Thu, 17 Sep 2015 14:46:13 GMT

Hi Sinan,

thanks a lot but I want to count the different Status like

ID ok not ok further status

3 1 0

2 0 1

based on the last time stamp.

Re: Count based on time stamp

sinanozdemir — Thu, 17 Sep 2015 14:48:59 GMT

Ok. You need to elaborate more because the above doesn't tell much.

What kind of status you want to classify IDs and what is your criteria for those statuses?

Re: Count based on time stamp

sunny_talwar — Thu, 17 Sep 2015 14:53:55 GMT

Not sure, but are you looking for this?

Expression: Sum(Aggr(If(Date(Max(TOTAL <ID> Date)) = Date, 1, 0), ID, Status))

Re: Count based on time stamp

sunny_talwar — Thu, 17 Sep 2015 14:56:57 GMT

Pivot Table view:

Re: Count based on time stamp

fatihsahin — Thu, 17 Sep 2015 15:30:02 GMT

Hi Sunny,

thanks for the input.

Seems like it doesnt work if I have two times the same line -

same id same status different date but also different further fields.

Than its not counting.

Re: Count based on time stamp

sunny_talwar — Thu, 17 Sep 2015 15:53:23 GMT

Can you add those additional lines to the sample data above to show the effect?

Re: Count based on time stamp

fatihsahin — Tue, 29 Sep 2015 14:26:38 GMT

Hi Sunny,

if you have for one id two data sets with two different dates but same status - it is not counting anything.

Regards