Sum(Counter_orders) is not equal in all tables

[pacoli2013]

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,

[sunny_talwar]

Check now

[kaushiknsolanki]

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

[pacoli2013]

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

[kaushiknsolanki]

Great,

Please mark relevant post as correct answer.

Regards,

Kaushik Solanki

[pacoli2013]

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?  

[kaushiknsolanki]

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

[pacoli2013]

Oke, thanks

[pacoli2013]

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

[kaushiknsolanki]

Kindly share your application to further investigate.

Regards,

Kaushik Solanki