topic Re: Expressions for Customer Potential in QlikView

Expressions for Customer Potential

pacoli2013 — Fri, 06 Jul 2018 12:40:34 GMT

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

Re: Expressions for Customer Potential

sunny_talwar — Fri, 06 Jul 2018 12:44:32 GMT

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

Re: Expressions for Customer Potential

pacoli2013 — Fri, 06 Jul 2018 12:58:10 GMT

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

Re: Expressions for Customer Potential

sunny_talwar — Fri, 06 Jul 2018 13:05:11 GMT

Right, so does that solve your issue?

Re: Expressions for Customer Potential

pacoli2013 — Mon, 09 Jul 2018 06:00:27 GMT

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

Re: Expressions for Customer Potential

sunny_talwar — Mon, 09 Jul 2018 11:16:03 GMT

What exactly are you hoping to see?

Re: Expressions for Customer Potential

pacoli2013 — Tue, 10 Jul 2018 06:28:20 GMT

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.

Re: Expressions for Customer Potential

sunny_talwar — Tue, 10 Jul 2018 12:18:52 GMT

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?

Re: Expressions for Customer Potential

pacoli2013 — Wed, 11 Jul 2018 06:07:49 GMT

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.

Re: Expressions for Customer Potential

sunny_talwar — Wed, 11 Jul 2018 10:43:20 GMT

Is this looking better?

Re: Expressions for Customer Potential

pacoli2013 — Wed, 11 Jul 2018 12:33:25 GMT

Hello Sunny,

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

Re: Expressions for Customer Potential

sunny_talwar — Wed, 11 Jul 2018 12:46:02 GMT

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))

Re: Expressions for Customer Potential

pacoli2013 — Wed, 11 Jul 2018 13:31:58 GMT

Thanks Sunny

Re: Expressions for Customer Potential

sunny_talwar — Wed, 11 Jul 2018 13:36:32 GMT

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

Best,

Sunny