how to avoid double counting of a record?

[#1]

Hi All,

We have a straight table having dimension as year and expression is count (Acton Number).

there is one Action number which is present in both 2013 and 2014. since the action first started in 2013, user do not want it to get counted in 2014 and it should be counted only in 2013. currently i use the below expression:

count(Distinct{<Year>}ActionNumber)

Dimension: Year

so above expression gives me result as

2013-41

2014-45

But i want 2014 to have just 44 as count.

Could someone please help me out with your suggestions?

Thanks & Regards

Jyothi

[MK_QSL · Accepted solution]

DATA:

Load * Inline

[

  Year, ActionNumber

  2012, A

  2013, A

  2014, A

  2012, B

  2013, C

  2012, D

  2013, E

  2014, F

];

Left Join(DATA)

Load ActionNumber, Min(Year) as MinYear Resident DATA Group By ActionNumber;

use below expression

COUNT(DISTINCT IF(Year = MinYear, ActionNumber))

[#3]

Thanks Manish!!

I am getting the count right now.

would you be kind enough to explain this logic to me please...?

[#4]

also, will this logic apply in all cases? like if I have a common record in 2013 and 2015, would this logic work?

Please suggest.

[Anonymous]

yes, it should work in that case too.

Load ActionNumber, Min(Year) as MinYear Resident DATA Group By ActionNumber;

basically, your original table is being grouped by ActionNumber and then the smallest value for year is being assigned to MinYear field using Min() function. left joining this new table to your original will create a new column in your original table with the field name MinYear which is what you will have to use in your charts instead of Year to get the smallest year value for a value in ActionNumber.

[#6]

Makes sense !! Thanks alot Jsaradhi