Error In Calculated Dimension Help

[pnn44794]

Hello:

I have the following Calculated Dimension.

I'm not sure why this is throwing an error.  Am I missing something simple?  The idea is to then use the Dimension Limit to show the largest 20 only.

Thanks in advance for any and all help.

[sunny_talwar]

1) Pivot table would be worse

2) Attached the qvw

Re: Error In Calculated Dimension Help

[pnn44794]

Thank you Sunny. So, it looks like I need to change my Load script by adding AutoNumber(project_id&order_date_quarter&Foundation&Foundation_1) as Key, and then add the LOAD Inline you have. If I need additional columns added I would just simply add them to the AutoNumber line, correct? And of course add a calculated dimension for each one.

I see two expressions. This one has the red circle with an white X in it. When I go into the editor for it, it says the Expression is Ok. I assume it’s ok and needed.

Num(Rank(TOTAL If(Dim = 1, Sum({<project_include_in_forecast = {1}, project_is_active = ,estimate_include_in_forecast=, estimate_is_active =, Foundation *= {'VSI'}>} effective_item_qty))))

[sunny_talwar]

1) You are correct about the load script changes

2) The crossed out expression is not needed, just doing my testing with individual piece of the code. That's how I build my expression. I build a piece at a time and then put them together for something which might work.

[pnn44794]

Ah. Ok. Makes sense and sounds like a good practice. I’m working on the updates now. As soon as I’m either done or before I leave for the day, I’ll mark your answer as correct. A big thank you again for your help. Very much appreciated.

[pnn44794]

I’m curious. I noticed on the Sort tab for the first column listed (=If(Dim = 1, Key), you’re using a Sort by Expression. For that expression, you’re dividing by -1E10. What is that and what does that do for the expression?

[sunny_talwar]

Basically used to make sure that "Other" is located at the bottom of the chart

[pnn44794]

That’s a nice trick. I’ll have to remember that. ☺ Thanks.

[pnn44794]

Hello Sunny:

This is my fault as I did not tell you about this.  I was focused on getting Other added.  Anyway, I have 3 more calculated dimensions which are effectively filtering out rows.  How can I get the If(Dim = 1..... wrapped around these correctly?  I tried adding the =If(Dim = 1, then the other If statement here, but it didn't work.  Here's the other expressions.  Also, Other is not showing up at all, but I may need these fixed to cause that to happen.

For DM #

If((IsNum([DM #]) and Len([DM #]) = 4 and [DM #] <> '99999') or (IsNum([DM #]) and Len([DM #]) = 5 and [DM #] <> '99999') or ([DM #] Like '88888*'), [DM #])

For project_name

If(wildmatch(project_name, '*HLIM*') = 0, project_name), project_name)

For order_date_quarter

If(order_date_quarter <> '2017-Q3' and order_date_quarter <> '2017-Q4' , order_date_quarter, null())

[pnn44794]

Hello Sunny:

Sorry for the delay in replying.  I've been away a few days.  Anyway......  While I can't get Other to work when I add additional columns, what you provided does indeed work, so I will mark that response as correct.  I'm probably missing something simple.  I'll see if I can get it to work with the additional columns added.

Thank you so much for your help!