topic Re: Incorrect values when associated with the dimension in QlikView

Incorrect values when associated with the dimension

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have created a table with outline as shown below:

Table1

Load
UniqueID,
'Year Forecast' as Dimension
Resident main;

Left Join(Table1)

Temp:

Load
UniqueID,
Manager1,

Manager2,

Manager3

Resident Table2;

Temp table is pulling the mentioned fields from another table called Table2 using Resident Load.

In the front end, I am building a Straight Chart with

'Dimension' as my Dimension

Expression, which is a sum of three different fields ( sum(field1) + sum(field2) + sum(field3)).

But I don't see the expression validating properly. I only get the value of sum(field1) in the chart.

Surprisingly, when I used the same expression in a Text Box, I see the right value.

What might be the issue here? It's literally the same fields and same expression. Then why do I see the correct value in the textbox but not in the chart?

Could it be the dimension?

I really appreciate it if someone answers it ASAP as I am stuck at this issue for a long time now and my next operation depends on building this.

Re: Incorrect values when associated with the dimension

trdandamudi — Sun, 24 Apr 2016 01:02:35 GMT

Is it possible to post a sample app ?

Re: Incorrect values when associated with the dimension

Sun, 24 Apr 2016 01:07:38 GMT

I wish I could. But the data I am pulling is huge and interconnected. The fields which I am using in SUM are generated using multiple operations and joins within the script.

Re: Incorrect values when associated with the dimension

trdandamudi — Sun, 24 Apr 2016 01:16:16 GMT

May be try this:

Dimension' as my Dimension

Expression: ( sum(Total field1) + sum(Total field2) + sum(Total field3) ).

Re: Incorrect values when associated with the dimension

sunny_talwar — Sun, 24 Apr 2016 01:17:58 GMT

Where is field1, field2 coming from? I don't see it in the Table2 load above? Please provide information which matches the script you are providing.

QlikCommunity Tip: How to get answers to your post?

Re: Incorrect values when associated with the dimension

johngouws — Sun, 24 Apr 2016 04:45:18 GMT

The text box is not aggregated to a Dimension whereas the Chart is.

Without more information I would say there is a "Key" issue. You will probably need work on the UniqueID key.

Something like "trim(upper(UniqueID))&trim(upper(?????)) as UniqueID"

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 13:27:16 GMT

field1 and field2 are coming from two different tables Sunny. Every table is connected via the UniqueID field.

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 14:41:45 GMT

Thank you so much Thirumala Dandamudi

Your expression solved my issue. I would really appreciate it if you could tell me what's the difference between

old exp: ( sum(field1) + sum(field2) + sum(field3)).

new exp: ( sum(Total field1) + sum(Total field2) + sum(Total field3) ).

Also, thanks to Sunny for your help

Re: Incorrect values when associated with the dimension

trdandamudi — Mon, 25 Apr 2016 14:51:46 GMT

old exp: ( sum(field1) + sum(field2) + sum(field3)). <--- It will consider the dimension ( It will Sum based on Dimension breakup)

new exp: ( sum(Total field1) + sum(Total field2) + sum(Total field3) ) <--- It will ignore the dimension while summing up.

Hope this is clear..

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 14:54:30 GMT

Yes, but when I select the dimension, the values are again changing.

I mean, the values look good in the overall view, but whenever I select my 'Year Forecast' dimension, only that row is shown, which is fine but the values are changing.

Re: Incorrect values when associated with the dimension

trdandamudi — Mon, 25 Apr 2016 15:07:12 GMT

The values will be changed based on your selections and it is how qlikview works. So based on your selections the dimension will be ignored while using the Total function.

If you think I misread your question can you please be little more specific on the question.

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 15:11:06 GMT

The new expression gives me the right values.

Year Forecast

But when I click on the Year Forecast Dimension, the $$ values are changing and not playing correctly with other fields.

This is a Straight Table

Re: Incorrect values when associated with the dimension

sunny_talwar — Mon, 25 Apr 2016 15:13:33 GMT

You want to ignore selection in Year Forecast field?

Sum(Total {<[Year Forecast]>} field1) + Sum(Total {<[Year Forecast]>} field2) + Sum(Total {<[Year Forecast]>} field3)

Re: Incorrect values when associated with the dimension

trdandamudi — Mon, 25 Apr 2016 15:18:49 GMT

Do you want to ignore the selection in "Year Forecast Field" ?

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 15:20:05 GMT

Sunny, if you see my script on how I am creating the Dimension- Year Forecast,

I might not be able to use the 'Year Forecast' in Set Analysis as it is an explicitly created dimension but not coming from a column.

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 15:21:45 GMT

'Year Forecast' is a Dimension that I am creating in the script. It is not present in any of the fields of any table. So when I click on Year Forecast dimension, I should be able to see the same value just as before I selected the Dimension. But the values are changing.

Re: Incorrect values when associated with the dimension

sunny_talwar — Mon, 25 Apr 2016 15:34:33 GMT

This I guess:

Sum(Total {<Dimension>} field1) + Sum(Total {<Dimension>} field2) + Sum(Total {<Dimension>} field3)

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 15:35:14 GMT

In a way, Yes Thirumala, I need to ignore the selection of Dimesion 'Year Forecast' so that the values don't change upon selecting 'Year Forecast'.

Re: Incorrect values when associated with the dimension

trdandamudi — Mon, 25 Apr 2016 16:09:08 GMT

Then I think as Sunny suggested might be the following:

Sum(Total {<Dimension>} field1) + Sum(Total {<Dimension>} field2) + Sum(Total {<Dimension>} field3)

Re: Incorrect values when associated with the dimension

Mon, 25 Apr 2016 16:11:02 GMT

As I said, the Dimension is not present in any table by default. I am creating the Dimension, 'Year Forecast' as Dimension in the script. In that case, I am not able to use it in Set analysis.