Count Distinct based on values in 2 rows

[#1]

Hello All,

I have a situation where I cannot write an expression to get the desired result.

My data is as follows:

HU Current Status Stage Dt Loaded

5001 13 07/09/09 NULL

5001 22 NULL 07/11/09

5002 13 07/13/09 NULL

In the above data I want to count only the HU that has the status 13 populated and not the other one which has both 13 and 22.

I.e in above case No. of HU's currently in staging = 1 even though both the Hu's have the 13 status'es populated. My data is an automated feed and extracts multiple lines for the same HU.

Appreciate the help that anyone can provide on this.

Regards,

Kiran

[#2]

Do you want to count only those HUs that have a Status 13 and nothing greater? Do you want to count only those HUs that don't have a loaded date?

For the first scenario:

Max(TOTAL <HU> [Current Status]) = 13

For the second:

IsNull(Max(TOTAL <HU> Loaded))

Depending on exactly how your data works some iteration of that should work. They key is the "TOTAL <HU>" inside the aggregation, which will get you the aggregation based on that field (HU).

[#3]

Hello Miller.

Thanks for the response and I am trying your answer one in this scenario. It gives me an output of 0 when I am expecting 2 in thsi test file.

also I understand this is a differnt format of an if statement, how can I add another condition within the saqme expression. Lets say for eg. when

CS = 1 and SD <

7/15/2009

thanks

[#4]

Forgot attachment.

[#5]

The statements I gave you work as chart expressions, but don't work in the text box. I'm not sure if it is possible to use them in a text box.

I've attached a sample that shows all HUs that have a max CS of 13 in a chart.

I'm not sure what you mean about adding a second condition question. You could add those conditions into the chart expression along with the Max CS expression. Maybe if you could give me some specifics on what you're looking to do here, I could give a more detailed answer.

[#6]

The way it is showing -1 for each of the registered value, I need to count from a set of HU's how many are in staging so wherever it is showing -1, i need to count that value.

Thanks.

[johnw · Accepted solution]

It's simpler than that if I understand the question. Chart expression can be just max(CS)=13, and text box expression can be -sum(aggr(max(CS)=13,HU)).

The "max(CS)=13" part of both expressions is just a conditional, so add as many conditions as you want. However, the conditions you gave are not conditions at the summary level, but conditions at the detail level, so they'd have to go INSIDE of the max() expression. And in this specific case, they are incompatible with what you originally asked for, so I can't give you a combined example. Well, I could combine your date check with the max(CS)=13, so that you're looking for for a max(CS)=13 for dates < 7/15/2009. Something like max(if(SD<date('7/15/2009'),CS))=13.

[#8]

Thank you.

This works perfectly for me.....:)