Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)
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)
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 =?
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
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!