Count based on time stamp

[fatihsahin]

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

[sinanozdemir]

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.

[fatihsahin]

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.

[sinanozdemir]

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?

[sunny_talwar]

Not sure, but are you looking for this?

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

[sunny_talwar]

Pivot Table view:

[fatihsahin]

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.

[sunny_talwar]

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

[fatihsahin]

Hi Sunny,

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

Regards