topic Re: Error In Calculated Dimension Help in QlikView

Error In Calculated Dimension Help

pnn44794 — Sun, 13 Oct 2019 00:31:06 GMT

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.

Re: Error In Calculated Dimension Help

sunny_talwar — Thu, 09 Nov 2017 19:05:43 GMT

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

Re: Error In Calculated Dimension Help

pnn44794 — Sun, 13 Oct 2019 00:32:41 GMT

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.

Re: Error In Calculated Dimension Help

vishsaggi — Thu, 09 Nov 2017 19:20:13 GMT

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)

Re: Error In Calculated Dimension Help

sunny_talwar — Thu, 09 Nov 2017 19:20:16 GMT

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)

Re: Error In Calculated Dimension Help

pnn44794 — Thu, 09 Nov 2017 19:52:41 GMT

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.

Re: Error In Calculated Dimension Help

pnn44794 — Thu, 09 Nov 2017 21:17:30 GMT

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:

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?

Re: Error In Calculated Dimension Help

sunny_talwar — Thu, 09 Nov 2017 23:29:47 GMT

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.

Re: Error In Calculated Dimension Help

pnn44794 — Fri, 10 Nov 2017 00:26:08 GMT

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.

Re: Error In Calculated Dimension Help

sunny_talwar — Fri, 10 Nov 2017 00:55:59 GMT

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

QlikView 11 Dimension Limit Functionality

Re: Error In Calculated Dimension Help

pnn44794 — Sun, 13 Oct 2019 00:27:16 GMT

No, that's not working for me. To be fair, I think maybe what I'm trying to do is not real clear.

Re: Error In Calculated Dimension Help

pnn44794 — Fri, 10 Nov 2017 02:03:29 GMT

P.S. Do you ever sleep? You always seem to be responding to various people. Just so you know, thank you! It's appreciated, at least by me.

Re: Error In Calculated Dimension Help

sunny_talwar — Fri, 10 Nov 2017 14:22:34 GMT

Would have been helpful if you shared a qvw file, but I created this chart... can you let me know what the expected output here would be?

Re: Error In Calculated Dimension Help

sunny_talwar — Fri, 10 Nov 2017 14:22:44 GMT

I do sleep my friend

Re: Error In Calculated Dimension Help

pnn44794 — Sun, 13 Oct 2019 00:28:33 GMT

Hello Sunny:

I would expect to see the first 20 rows. Then, I'm looking to have one more row for Others (all the other rows combined which would have a total quantity of 3,953 (15,321 - top 20 quantity of 11,368). When I filter by order_date_quarter, I should get a top 20 for that quarter and one Others row. I also need totals to show.

Does this make sense?

Re: Error In Calculated Dimension Help

sunny_talwar — Fri, 10 Nov 2017 15:27:30 GMT

It does.... one quick question... do you have multiple dimensions in your chart (the way I showed). Also, are you using pivot table or straight table??

Re: Error In Calculated Dimension Help

pnn44794 — Sun, 13 Oct 2019 00:29:56 GMT

Sorry for the delay. Yes, I do have several dimensions in the chart.

Re: Error In Calculated Dimension Help

pnn44794 — Fri, 10 Nov 2017 16:12:15 GMT

Sorry. I forgot to answer your other question. I'm using a straight table.

Re: Error In Calculated Dimension Help

sunny_talwar — Fri, 10 Nov 2017 17:29:42 GMT

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

Re: Error In Calculated Dimension Help

pnn44794 — Fri, 10 Nov 2017 17:46:10 GMT

Would a pivot table work better? I’m open to it. ☺ If not, this is better than what I have. You only sent an image, right? No code?