Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello:
I need to do a distinct count of Template Order ID when LOB Template Order does not contain a dash. Sample data is attached. Template Order ID is a calculated dimension using =if(index([LOB Template Order],'-')=0,[LOB Template Order]). This filters out any Template Order ID's that have a dash. In the attached sample data, I should wind up with 644 distinct Template Order ID's. The expression I'm trying to use is =Count(if(index([LOB Template Order],'-')=0, [Template Order ID])). Template Order ID is being flagged as a bad field name which I'm guessing is because it's a calculated dimension and not a real field that's loaded. I think I'm close, but I'm obviously missing something and something probably simple.
As always, any and all help is appreciated. Thanks in advance.
May be this
Count(DISTINCT {<[LOB Template Order] -= {"*-*"}>}[Template Order ID])
Or this
=Count(DISTINCT If(Index([LOB Template Order], '-') = 0, [Template Order ID]))
I get just zeros with this expression. Trying the other.
I have attached a sample with the image showing that both the expressions give 644 as output.... isn't that what you wanted?
It is indeed. I’m probably doing something wrong. Give me a few minutes to download your qvw and compare against mine.
Ok. My fault for not explaining fully. Template Order ID is a calculated dimension and thus gets a syntax error when I enter the expression, even in a text box (Bad field name(s)). I can export the data without this expression, select the Template Order ID field and remove duplicates and wind up with 644. What I would like to do is have the field, let's call it Distinct Template Order IDs, and then that field be a field where I can sum it. So, if it's not distinct, the value is 0, otherwise it's 1 (meaning LOB Template Order is a distinct value).
Make sense?
How is it calculated?
I mean how is Template Order ID calculated?
The calculated dimension for column Template Order ID is =if(index(,'-')=0,[LOB Template Order]).