Expressions for Customer Potential

[pacoli2013]

Hello

I have a document with two tables Customer Potential Totals and Potential SalesCategory. And three controle-tables: Customer, Customer Potentials and Customer Unspec Potentials.

The totals of the amounts in first two tables should be the totals from 01-01-2018 until now. There are no time filters and when the document is started the current year and month are set. But month should be of no influence on the amounts.

The totals of the first table called Customer Potential Totals, are correct.

Dimension in this table is CustomerID (when it is not equal as Not Set), the expressions are Sum of the different amounts, this is possible because the amounts are direct related with CustomerID)

The Second table (Potential SalesCategory) has as dimension CustSpecPot.SalesCategoryCode and is related with CustomerID.

The totals of both Tables should be equal. Column Spec.Potential is in both tables equal because the amounts are coming from table CustSpecPot and the relation is CustomerID.

As expressions for the other three columns in table  Potential SalesCategory I tried

Sum({$ < Year = {$(= max(Year))} > } (SumCustomerLJAmountEUR)) for Actual 2018

Sum({$ < Year = {$(= max(Year))}, Month=, MonthShortName= > } SumCustomerBudgetAmount) for Budget

and

Sum({$ < Year = {$(= max(Year))}, Month=, MonthShortName= > } Aggr(Sum({$ < Year = {$(= max(Year))}, Month=, MonthShortName= > } CustUnspecPot.RelevantPotential) ,CustomerID))

for NonSpecPotential

But this expressions don’t give the same result as in the first table

I hope that someone can give me expressions which give the correct results

Thanks in advance

Regards Court

I have added a document to show my problems

[sunny_talwar]

I think the problem is in CustSpecPot.SalesCategoryCode field... it appears to have some nulls which you suppressed by checking 'Suppress When Value Is Null'... as soon as I uncheck that... the value matches

[pacoli2013]

Hello Sunny,

I checked it: for CustSpecPot.SalesCategoryCode Suppress When Value is Null is activated but when I inactivate it I get the same results as before

Court

[sunny_talwar]

Right, so does that solve your issue?

[pacoli2013]

Hello Sunny, no it didn't solve my problem or issue

[sunny_talwar]

What exactly are you hoping to see?

[pacoli2013]

Hello Sunny,

What I hope to see is that the totals for Actual and Budget in table Potential SalesCategory are the same as in the table Customer Potential Totals. Of course the values per SalesCategoryCode should also be correct.

The problem I have is that Actual and Budget are direct related with CustomerID (see table Customer), they are part of the Customer table. SalesCategoryCode is related with Customer by CustomerID (see table Customer Potentials). Non-SpecPotential is the third table also related by CustomerID. 

One Customer can have more then one SalesCategoryCode

Second problem I think is that both tables (Customer Potentials and Customer UnSpec Potentials) can have the value NotSet. And this values should not be in the table Potential SalesCategory (if possible).

So, I don’t no if my problem can be solve, I have my doubts. When you can, it would be super

Thanks in advance

Regards Court.

[sunny_talwar]

I guess I am still not sure I understand your issue...

1) Totals do match now

2) You think the row wise numbers are not correct? What should the correct numbers be? can you provide this information in an Excel file?

[pacoli2013]

Hello Sunny,

The Potential values / amounts are correct but the Totals for Actual and Budget are not and but they should be. Both (Actual and Budget) are correct in table Customer Potential Totals. In the other table both values are almost double the value as in the first table. When I take one SalesCategory all values/amounts are correct in both tables.

Example when I choose 10100000 -Schließer I get in both tables the values:

Spec.Potential: 53.367

Non Spec Potential: 1.307.500

Actual 2018: 70.057

Budget 2018: 216.942

I think the problem is the relation between CustomerID and SalesCategory in the second table Potential SalesCategory. because when I choose a CustomerID (example 22300000775) I get 318 as Actual in the first table but 1273 in the second. That is 4 times 318, reason is that this Customer has 4 SalesCategoryID's.

Question is can this be solved with Set Analysis so that when I choose a Customer I get the right amounts for Actual and Budget in the second table Problem is that there is no key because in the Customertable there is no field SalesCategoryID.

[sunny_talwar]

Is this looking better?