topic Re: Group customer codes to get total sales in QlikView

Group customer codes to get total sales

Anonymous — Mon, 08 Feb 2016 18:12:05 GMT

Hello-

I am trying to get a sum of sales by a customer grouping however that total sale is not held as a field in our software. For example I have certain customers I would like to group together and get their sales by MTD and YTD.

Cust code:

MEN1

MEN2

Then I would like a total

LWE1

LWE2

LWE3

Then I would like a total

HD1

HD2

HD3

Then I would like a total

It's not for all customers though, only some of them. Once I get the sums per customers, I would like to then put those totals by customer on a graph.

MEN Total

LWE Total

HD Total

Can anyone tell me how I can group these together to get their sales by MTD/YTD?

Thank you

Jennifer

Re: Group customer codes to get total sales

Anonymous — Mon, 08 Feb 2016 18:38:29 GMT

Look like you need to create a new dimension Customer by removing the numbers from your field Cust Code.

Would that work ?

Re: Group customer codes to get total sales

PradeepReddy — Mon, 08 Feb 2016 18:47:28 GMT

we can achieve this 2 ways.

1) Script:

Load *,

IF(Wildmatch(Customer,'Men*'),'Men', IF(Wildmatch(Customer,'LWE*'),'LWE',IF(Wildmatch(Customer,'HD*'),'HD'))) as Customer_Grp

from Customers;

Dimension : Customer_Grp

Expression : sum(Sales)

2) Front End

Calculated Dimension:

IF(Wildmatch(Customer,'Men*'),'Men',

IF(Wildmatch(Customer,'LWE*'),'LWE',

IF(Wildmatch(Customer,'HD*'),'HD')))

Expression: sum(Sales)

Re: Group customer codes to get total sales

Anonymous — Tue, 09 Feb 2016 15:43:47 GMT

I am fairly new to this, especially the scripting. If I already have this being loaded (my customer master table), do I just add the above to this or will this be a separate QVD? The field is actually the CUSTOMER_CODE so would I use that where you reference Customer in the above formula?

Thanks for any help!

Qualify *;

Unquality CUSTOMER_CODE;

Customers:

Load

ADDRESS_1

ADDRESS_2

CUSTOMER_CODE

etc.

Sql Select *

FROM "CUSTOMER MASTER FILE";

Store Customers into .\QVD\Customers.QVD;

Drop TABLE Customers;

Re: Group customer codes to get total sales

Anonymous — Tue, 09 Feb 2016 17:35:24 GMT

I would do it something like this :

Customers:

Load

PurgeChar( [CUSTOMER_CODE] , '0123456789' ) as [Customer Prefix]

ADDRESS_1

ADDRESS_2

CUSTOMER_CODE

etc.

Sql Select *

FROM "CUSTOMER MASTER FILE";

You can find the PurgeChar() function in the QV Desktop Help. Basically what it does above is remove all the numbers from the string creating a new field called [Customer Prefix]

Re: Group customer codes to get total sales

Anonymous — Tue, 09 Feb 2016 18:17:35 GMT

I think that would work if I needed to do this for all customer codes, however I only have a 'group' of customers I need to do this for, not all of them.

Re: Group customer codes to get total sales

Anonymous — Tue, 09 Feb 2016 18:34:45 GMT

Can define how you would identify if a customer is in the 'group' ?

Re: Group customer codes to get total sales

Anonymous — Tue, 09 Feb 2016 18:44:49 GMT

We have certain accounts that we want to track on their own sheet, so out of all of our customers I have a list of about 12 accounts that I need to be able to get total sales on for MTD and YTD (basically adding up each of our divisions sales):

Cust Code:

MEN1

MEN2

Need total sales and plot a point on a graph for the total called something like: MEN

LWE1

LWE2

LWE3

Need total sales and plot a point on a graph for the total called something like LWE

This would apply to like 4 more accounts.

Re: Group customer codes to get total sales

Anonymous — Tue, 09 Feb 2016 19:02:34 GMT

if ( index ( 'MEN1' , MEN2' , 'LWE1' , '...add the rest of your values' )

PurgeChar( [CUSTOMER_CODE] , '0123456789' ) as [Customer Prefix]

As in, if CUSTOMER_CODE identified as in your 'group', set [Customer Prefix] value to characters, otherwise null()

Re: Group customer codes to get total sales

Anonymous — Tue, 09 Feb 2016 20:00:34 GMT

Ok, I have tried this and got a script error, is it something simple I don't see?

Error in expression:Index takes 2-3 parameters

Customers:

Load

"ADDRESS_1",

"ADDRESS_2",

"ACRONYM",

"CUSTOMER_CODE",

If (index('MENCP1', 'MENCP2', 'HDCRP1', 'HDCRP2', 'HDCRP3')

PurgeChar([CUSTOMER_CODE],'0123456789') AS [CUSTOMER_PREFIX]),

"CUSTOMER_TYPE",

Re: Group customer codes to get total sales

Anonymous — Wed, 10 Feb 2016 09:56:39 GMT

Ooops! Sorry, completely messed that up. This should be better :

Customers:

Load

"ADDRESS_1",

"ADDRESS_2",

"ACRONYM",

"CUSTOMER_CODE",

if ( wildmatch ( '[CUSTOMER_CODE] , 'MEN*', 'HDC*', 'LWE*' ) ,

PurgeChar( [CUSTOMER_CODE] ,'0123456789') ,null() ) as [CUSTOMER_PREFIX] ),

"CUSTOMER_TYPE"

etc......

Sql Select *

FROM "CUSTOMER MASTER FILE";

Re: Group customer codes to get total sales

Anonymous — Wed, 10 Feb 2016 15:53:38 GMT

That did it! This will be helpful for some other customers I may need to do something similar with. Thank you for your time and help!