topic Re: Sum(Counter_orders) is not equal in all tables in QlikView

Sum(Counter_orders) is not equal in all tables

pacoli2013 — Thu, 31 Aug 2017 15:26:03 GMT

Hello,

I have a dashboard with five different tables.

In my script I have created a counter for orders

In every table this counter is coming back (Acc.Pos). The sum is calculated in the expression (Sum of Rows).

You would think that the sum of counters is in every table equal, but in two tables the sum is 5250, in two tables 5236 and in the last table 5211 for the month January.

Table 01: dimension is DateTaskForce and the expression is = Sum({$ < Year = {$(= max(Year))} > } Counter_Orders)

Table 02: dimension is DateTaskForce and the expression is = = Sum(Counter_Orders)

Table 03: dimension is DayName and the expression is = = Sum({$ < Year = {$(= max(Year))}> } Counter_Orders)

Table 04: dimension is DayName and the expression is = = Sum({$ < Month = {$(= max(Month))}> } Counter_Orders)

Table 05: dimension is MonthCombiAll and the expression = Sum({$ < Year = {$(= max(Year))}, Month=, MonthShortName= > } Counter_Orders)

DateTaskForce and MonthCombiAll come from the facttable, DayName from the Calendar. Calendar and Facttable are related though the field Date = DateTaskForce.

When you check some date together 05-01-2017 – 13-01-2017 you get differences between the tables. When you check every date separate, you have no differences

So when you check 10 dates separate (date by date) you have no differences, why differences when take all dates together.

I thought maybe the Set Analysis had something to do with it, so I have made a copy of the dashboard and changed the expressions in every table into = Sum(Counter_Orders). Result is the same.

When you filter Schloss from ProductionPool, you get for

Friday 6-1-2017 in all tables 17

Friday 13-1-2017 in all tables 38

When you take both dates together, you get in table 01 and 02 a total of 55 (what is correct) in the other tables you get 54 (???)

The total in the table CheckDate is 55 so 55 is correct I think

I can’t explain, it hope someone can

Thanks in advance

Regards Court,

Re: Sum(Counter_orders) is not equal in all tables

sunny_talwar — Thu, 31 Aug 2017 15:41:14 GMT

Check now

Re: Sum(Counter_orders) is not equal in all tables

kaushiknsolanki — Thu, 31 Aug 2017 15:41:53 GMT

Hi,

It is because same value is counted on both the days i.e on 6th and 13th.

SO if you look at the "Auftragsnr_regel" field and the value 61520656872 it is counted on both the days.

Thus when you have date as dimension those will be counted twice and when you dont have date field it will be counted once. Thus you see a difference of 1 value.

You can try this expression to keep same total in all charts.

= Sum(Aggr(Sum({$ < Year = {$(= max(Year))}> } Counter_Orders) ,DateTaskForce))

Regards,

Kaushik Solanki

Re: Sum(Counter_orders) is not equal in all tables

pacoli2013 — Fri, 01 Sep 2017 07:12:42 GMT

I have checked it, it works.

It also shows me that I have to learn a lot about working with Set Analysis

Thanks a lot for your support

Regards Court

Re: Sum(Counter_orders) is not equal in all tables

kaushiknsolanki — Fri, 01 Sep 2017 07:16:19 GMT

Great,

Please mark relevant post as correct answer.

Regards,

Kaushik Solanki

Re: Sum(Counter_orders) is not equal in all tables

pacoli2013 — Fri, 01 Sep 2017 08:09:31 GMT

Hello,

I have remarked it as Helpful, that is the only Action I have.

I have build in your solution in the five tables. The only little thing I have is in table 5.

the two expressions Re.Pos and Avr Re.Pos are now:

Re.Pos : = = Sum(Aggr(Sum({$ < Year = {$(= max(Year))}, Month=, MonthName=, MonthShortName=> } Counter_Orders) ,DateTaskForce))

Avr Re.Pos: = Sum({$ < Year = {$(= max(Year))}, Month=, MonthName=, MonthShortName= > } ProductValue) / Sum(Aggr(Sum({$ < Year = {$(= max(Year))}, Month=, MonthName=, MonthShortName=> } Counter_Orders) ,DateTaskForce))

It's a table with months and values, so our users want to see all the values for every month, timefilter month/monthshortname should have no influence.

Wenn I use the monthfilter, with the two expressions above I only see the values for the filtered month.

I thought that by using Month=, MonthName=, MonthShortName= in my Expression, the month-filter would have no influence. Do I have to use all timefilters (like year, day etc) in my Expression?

Re: Sum(Counter_orders) is not equal in all tables

kaushiknsolanki — Fri, 01 Sep 2017 08:15:30 GMT

Hi,

You need to use all fields which user will use to make a selection.

Say for example if you have given month and year filter then both should be neglected in set analysis.

Regards,

Kaushik Solanki

Re: Sum(Counter_orders) is not equal in all tables

pacoli2013 — Fri, 01 Sep 2017 08:20:42 GMT

Oke, thanks

Re: Sum(Counter_orders) is not equal in all tables

pacoli2013 — Fri, 01 Sep 2017 09:01:09 GMT

Hello,

The Expression for Re.PO is now = Sum(Aggr(Sum({$ < Year = {$(= max(Year))}, Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName= > } Counter_Orders), DateTaskForce))

Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName= are all the filters and alias of filters (like Month is the monthnumber, alias of MonthShortName)

But when I set the filter on Aug, the values for the other months disappear. I still don't understand

Re: Sum(Counter_orders) is not equal in all tables

kaushiknsolanki — Fri, 01 Sep 2017 09:03:48 GMT

Kindly share your application to further investigate.

Regards,

Kaushik Solanki

Re: Sum(Counter_orders) is not equal in all tables

pacoli2013 — Fri, 01 Sep 2017 09:23:10 GMT

Hello,

I will do that, but I have to make a new discussion because I don't have the 'Use Advanced Editor' -Option. I will explain which table has to react on what filter

Regards Court

Re: Sum(Counter_orders) is not equal in all tables

bharani8 — Fri, 01 Sep 2017 09:39:42 GMT

While replying, u can find that option on Top Right.. like below..

Re: Sum(Counter_orders) is not equal in all tables

pacoli2013 — Fri, 01 Sep 2017 09:44:07 GMT

Hello I don't have that option

Re: Sum(Counter_orders) is not equal in all tables

kaushiknsolanki — Fri, 01 Sep 2017 09:46:33 GMT

Hi,

You should Go to the actual post then you will see. You wont see then you reply through your inbox.

Open the post in new window and then check that option.

Regards,

Kaushik Solanki

Re: Sum(Counter_orders) is not equal in all tables

sunny_talwar — Fri, 01 Sep 2017 11:06:18 GMT

Check here

Uploading a Sample