Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner
Partner

Error In Calculated Dimension Help

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.

1 Solution

Accepted Solutions
sunny_talwar

This is probably not the best, but can't think of a better way to do this

Capture.PNG

View solution in original post

28 Replies
sunny_talwar

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

pnn44794
Partner
Partner
Author

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
Champion III
Champion III

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
Partner
Partner
Author

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
Partner
Partner
Author

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
Partner
Partner
Author

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