Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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";
Look like you need to create a new dimension Customer by removing the numbers from your field Cust Code.
Would that work ?
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)
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;
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]
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.
Can define how you would identify if a customer is in the 'group' ?
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.
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()
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",