topic Re: Error in the calculated dimension (Histogram) in QlikView

Error in the calculated dimension (Histogram)

paulinhok14 — Wed, 25 Nov 2020 16:16:04 GMT

Good afternoon guys!

I would like to make a histogram, counting differently the codes that I have that reach a percentage value (accuracy).

Each code has a percentage value, but sometimes more than one code has the same value.

This formula for accuracy is used in other graphs and works perfectly, as follows:

= if (sum (Demand) = sum (Forecast), 1,

if (sum (Demand) = 0 and sum (Forecast) <> 0, 0,

if (sum (Forecast) = 0 and sum (Demand) <> 0, 0,

if (sum (Demand)> sum (Forecast), Round (1+ Fabs (sum (Demand) - sum (Forecast)) / sum (Forecast), 0.1)

if (sum (Forecast)> sum (Demand), Round (sum (Demand) / sum (Forecast), 0.1)

)))))

In Expression, I would like you to have the code count corresponding to that percentage. The expression would be:

= count (distinct [Code&Loc])

What I would like to have is something like:

What actually happens in the chart is a "// Error in the calculated dimension"

Can anyone tell me where I'm going wrong?

Re: Error in the calculated dimension (Histogram)

Gysbert_Wassenaar — Tue, 06 Nov 2018 12:36:27 GMT

Your expression if(sum .... etc ...) needs to be calculated over a set of one or more dimensions if you want to create a calculated for your histogram. You need to use the aggr function for that:

= Aggr( if (sum (Demand) = sum (Forecast), 1,

if (sum (Demand) = 0 and sum (Forecast) <> 0, 0,

if (sum (Forecast) = 0 and sum (Demand) <> 0, 0,

if (sum (Demand)> sum (Forecast), Round (1+ Fabs (sum (Demand) - sum (Forecast)) / sum (Forecast), 0.1)

if (sum (Forecast)> sum (Demand), Round (sum (Demand) / sum (Forecast), 0.1)

))))), Dim1, Dim2, ..., DimN)

Replace Dim1, Dim2, ..., DimN with the dimensions over which the sums need to be calculated

Re: Error in the calculated dimension (Histogram)

paulinhok14 — Tue, 06 Nov 2018 15:51:54 GMT

Gysbert thank you, it works perfectly!

Only for understanding purposes, why do I need to do this if the relation of my expression with the dimension is 1 to 1?

Re: Error in the calculated dimension (Histogram)

Gysbert_Wassenaar — Wed, 07 Nov 2018 08:50:01 GMT

And how is Qlikview to know that unless you tell it what that dimension is? Any field in your document could be the dimension over which the expression should be aggregated. How does sum(Demand) tell Qlikview which dimension it should use to calculate the results?

Re: Error in the calculated dimension (Histogram)

paulinhok14 — Wed, 07 Nov 2018 16:45:19 GMT

Well, if my expression is count ([Code&Loc]), it is supposed to aggregate into these counts, isn't it?

If I want to display the data aggregated as intervals now, how can I do it if my dimension is already aggregated?

For example if I want to put it like: a count of how many codes have the accuracity between 0 and 75%, 75% and 125%, above 125...

In this case the class function won't work because the step is not fixed, this can't be solved with Aggr expression?

Re: Error in the calculated dimension (Histogram)

Gysbert_Wassenaar — Thu, 08 Nov 2018 07:26:58 GMT

Well, if my expression is count ([Code&Loc]), it is supposed to aggregate into these counts, isn't it?

No, it is suppose to count how many combinations of Code&Loc exist per ...something. That could be countries or product groups or age classes or whatever. Never per Code&Loc because that is always 1.

If I want to display the data aggregated as intervals now, how can I do it if my dimension is already aggregated?

Which already aggregated dimension are we talking about now? The calculated dimension with the aggr I posted above? You can group that however you want. You could use nested if statements to create the intervals you need.

Re: Error in the calculated dimension (Histogram)

paulinhok14 — Thu, 08 Nov 2018 10:52:26 GMT

No, it is suppose to count how many combinations of Code&Loc exist per ...something. That could be countries or product groups or age classes or whatever. Never per Code&Loc because that is always 1.

Ok got it, I thought my calculated dimension was already agreggating by [Code&Loc]...

Which already aggregated dimension are we talking about now? The calculated dimension with the aggr I posted above? You can group that however you want. You could use nested if statements to create the intervals you need.

Yes, the same we discussed above, my chart worked perfectly using the Aggr by the field that my expression is counting, as you well told me:

= Aggr (if ( sum (Demand) = sum (Forecast), 100,

if ( sum (Demand) = 0 and sum (Forecast) <> 0, 0,

if ( sum (Forecast) = 0 and sum (Demand) <> 0, 0,

if ( sum (Demand) > sum (Forecast), Round ( (1+ Fabs (sum (Demand) - sum (Forecast) ) / sum (Forecast) ) *100 , 0.1),

if ( sum (Forecast) > sum (Demand), Round ( ( sum (Demand) / sum (Forecast) ) *100 , 0.1)

)

), [Code&Loc])

Now the thing is, if this dimension is already aggregated on [Code&Loc], how can I aggregate it on new nonexistent field?

I mean:

if I want to put it like: a count of how many codes have the accuracity (my whole dimension above means accuracity) between 0 and 75, 75 and 125, above 125...

There is no field called Accuracity, so I can't just do it like : if (Accuracity > 75, etc....)

In order to nest ifs, I would need to Aggr it again?

Re: Error in the calculated dimension (Histogram)

Gysbert_Wassenaar — Thu, 08 Nov 2018 10:56:23 GMT

No, you put the grouping logic inside the aggr and around the current outer if(...)