Count Set Analysis

[Diinkant]

Hi, 

I am trying to count distinct DocumentLocation based on a set of requirements. This is what I have so far.

Count({<LocationCode = {"0000","0004","0003"},
CreatedTime = {"<10:00:00 AM", ">3:00:00 PM"},
[Time Shipped] = {"<10:00:00 AM"},
[Date Shipped] = DocumentDate>} DISTINCT DocumentLocation)

If I take out Date Shipped = DocumentDate, it will work. So, I know that is the issue. I have been reading that you can compare 1 dimension to another in a set analysis. Is this still the case or am I doing something wrong?

Any help please.

[Nagaraju_KCS · Accepted solution]

Try this

Count({<LocationCode = {"0000","0004","0003"},
CreatedTime = {"<10:00:00 AM", ">3:00:00 PM"},
[Time Shipped] = {"<10:00:00 AM"},
DocumentLocation={"=[Date Shipped]= DocumentDate"}>} DISTINCT DocumentLocation)

[Nagaraju_KCS]

if both the fields are in the same table, you can create a flag in the script:

If([Date Shipped]= DocumentDate, 1, 0) as Flag

and then this

Count({<LocationCode = {"0000","0004","0003"},
CreatedTime = {"<10:00:00 AM", ">3:00:00 PM"},
[Time Shipped] = {"<10:00:00 AM"},
Flag={1}>} DISTINCT DocumentLocation)

[Diinkant]

I have joined the data into the same table, so i will try the second option. 

I tried the first option and that seemed to pull numbers through. Unsure if accurate but will check. Is there any reason why you used DocumentLocation =?

[Nagaraju_KCS]

Your count depends on DocumentLocation, so DocumentLocation={"=[Date Shipped]= DocumentDate"} is used

or 
If you want to compare field values on a per record base, set analysis is not the way to go,

use the below one script level

=If([Date Shipped]= DocumentDate, 1, 0) as Flag

[Diinkant]

Mate, you're an absolute legend! The first one works, and I had the second one there to check the data.

Thanks mate, I really appreciate it!