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.
Ignore the second part regarding Excel and exporting a number format. I made the field a Number under the Number tab. Looks like it works.
Yes, you are right about the first part. That is the only difference between the two charts
Thank you Sunny. And thank you so much for your help with this. I will mark your last answer as correct. Thanks again.
Can this expression, Aggr(Count(DISTINCT {<[LOB Template Order] -= {"*-*"}>}[LOB Template Order]), [LOB Template Order]), limit the amount of rows displayed?
Not sure I understand
If you look at the sample data I sent last night, it had 6,007 rows before I added that expression. After adding the expression, I now have 5,624 rows. It could be something has changed on my side and some kind of clean up was done (I’m trying to find out), but I also wanted to see if the expression could also prevent rows from displaying.
I am not sure where do you have 6007 rows of data... I see 6075 in the attached sample and when I exported the right chart, I see 6077 rows - 2 at the top to get 6075 rows.... What am I missing?
You’re not missing anything. It’s me. I’ve made a change somewhere that’s causing a problem. If I find it and need help, I’ll open a new thread. Sorry for the confusion.