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]

Sum, Avg, Min, Max, etc are aggregations which require Aggr() function when used as a dimension....

[pnn44794]

Thanks for the reply Sunny.

The editor says there's an error in the expression and there's a red squiggly line under the last parentheses.

[vishsaggi]

You have to give your dimension fields here:

=Aggr(Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'VSI'}>} effective_item_qty), yourdimensionfield1, dim2)

[sunny_talwar]

You need a dimension with Aggr() function... the dimension over which you want to calculate this sum

=Aggr(Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'VSI'}>} effective_item_qty), Dimension/s Name Here)

[pnn44794]

Ah! Got it. I may have another problem now though, where I’m getting the same quantity 4 times it appears for each DM # in the data set. I’ll verify that. I have to attend to something else at the moment before I do though.

[pnn44794]

Ok.  I believe I figured out the same value for multiple DM #.  I needed to add all dimensions used in the straight table.  So, the calculated dimension now has an expression that looks like this:

=Aggr(Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'VSI'}>} effective_item_qty), [DM #], project_id, project_name, order_date_quarter, Foundation, Foundation_1, effective_reporting_lob_2,

estimate_item_primary_location_name)

Two problems have become apparent.  One, I cannot get a total for the above to display.  It only shows the word Total on the last row.  Under Dimension Limits, I have the Show Total box checked and Label: = <use default>.  Under Presentation, Totals on Last Row is checked.  What might I be missing?

I wanted to use this calculated dimension so I could limit, via Dimension Limits, the list to the Largest 10.  Here's where my second problem comes in and this may not be doable.  I want the largest 10 and then one more row for Others (everything else combined into one row).  Instead, I get rows for everything else and in the quantity column, I get Others.  Is what I want to do doable?

[sunny_talwar]

1) Dimensions don't really have totals

2) You can limits using expressions also, and would encourage you to do that as calculated dimensions are resource extensive.

[pnn44794]

Thanks Sunny.  Regarding # 1, good to know.  I can't do what I'm trying to do essentially via a calculated dimension (show total).

Also good to know on a calculated dimension being resource intensive.  Thanks for that guidance.

So, is there a way to do what I'm trying to do?  Show top 10 and then one row for all "Others"?  It seems like probably not and if not within a straight table, that's fine.

[sunny_talwar]

Usually you can use Dimensions Limit tab to show Top 10 and Others... is that not working for you?

QlikView 11 Dimension Limit Functionality