Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This is probably not the best, but can't think of a better way to do this
Sum, Avg, Min, Max, etc are aggregations which require Aggr() function when used as a dimension....
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.
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)
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)
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.
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?
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.
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.
Usually you can use Dimensions Limit tab to show Top 10 and Others... is that not working for you?