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

[pacoli2013]

Hello Sunny,

That is looking great, how did you do it. Which Dimension(s) and Expressions did you use

[sunny_talwar]

Dimension

CustSpecPot.SalesCategoryCode

Expression

Sum(CustSpecPot.Amount)

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

If(Dimensionality() = 0, Sum(Aggr(Sum({$<Year = {$(= max(Year))}, CustomerID -= {'NotSet'}>} SumCustomerLJAmountEUR), CustomerID)), Sum({$<Year = {$(= max(Year))}>} SumCustomerLJAmountEUR))

If(Dimensionality() = 0, Sum(Aggr(Sum({$<Year = {$(= max(Year))}, Month, MonthShortName>} SumCustomerBudgetAmount), CustomerID)), Sum({$<Year = {$(= max(Year))}, Month, MonthShortName>} SumCustomerBudgetAmount))

[pacoli2013]

Thanks Sunny

[sunny_talwar]

No problem at all... I am glad I was able to help.

Best,

Sunny