Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner - Specialist
Partner - Specialist

Help With Set Expression - Syntax Error (and Possible Logic Error)

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.

27 Replies
pnn44794
Partner - Specialist
Partner - Specialist
Author

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.

sunny_talwar

Yes, you are right about the first part. That is the only difference between the two charts

pnn44794
Partner - Specialist
Partner - Specialist
Author

Thank you Sunny. And thank you so much for your help with this. I will mark your last answer as correct. Thanks again.

pnn44794
Partner - Specialist
Partner - Specialist
Author

Can this expression, Aggr(Count(DISTINCT {<[LOB Template Order] -= {"*-*"}>}[LOB Template Order]), [LOB Template Order]), limit the amount of rows displayed?

sunny_talwar

Not sure I understand

pnn44794
Partner - Specialist
Partner - Specialist
Author

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.

sunny_talwar

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?

Capture.PNG

pnn44794
Partner - Specialist
Partner - Specialist
Author

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.