Group customer codes to get total sales

[Anonymous]

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

[Anonymous]

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

Would that work ?

[PradeepReddy]

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)

[Anonymous]

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;

[Anonymous]

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]

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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",