Quick Question

[jadams123]

Hi All:

Question 1:

I want too:

if (today -1) = mon then 0, if (today -1) = tue then 2, if (today -1) = wed then 3...and so on

Question 2:

I want to be able to do this:

How can I group:

1) Group anything other than CX1, CX2, CX3, CX4 in "Other CX type East"

2) Group anything other than CX5, CX6, CX7, CX8 in "Other CX type West"

and so on...

Group	Customer Type	Sales
EAST	CX1	$100.00
EAST	CX2	$200.00
EAST	CX3	$300.00
EAST	CX4	$400.00
EAST	Other CX Type EAST	$500.00
Total EAST		$1,500.00
WEST	CX5	$600.00
WEST	CX6	$700.00
WEST	CX7	$800.00
WEST	CX8	$900.00
EAST	Other CX Type WEST	$500.00
Total West		$3,500.00

This is my current formula. This obviously just shows sales for CX's

= Sum( {$<GLYEAR = {2017},

CUSTOMER_TYPE = { CX1, CX2, CX3, CX4, CX5, CX7, CX8 },

CREDIT_CODE = {YES}>} AMOUNT )

Thanks,

J

[vishsaggi]

May be below? Question 1 and Question 2 you can create something like:

LOAD ....,

Pick(Match(WeekDay(Today()-1), 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'), 0,1,2,3,4,5,6) AS DayNo,

IF(Group = 'EAST'   AND Not Match(CustomerType, 'CX1','CX2','CX3','CX4'), 'Other CX Type EAST',

IF(Group = 'WEST'  AND Not Match(CustomerType, 'CX5','CX6','CX7','CX8'), 'Other CX Type WEST')) AS                                                                                                                                                  AS NewCustTypeField

FROM ....;

[jadams123]

I am not a developer...I can only do calculated fields... first formula work but the second formula is not working..

= Sum( {$<GLYEAR = {2017},

if(CBT = {'EAST'} AND Not Match(CUSTOMER_TYPE_DESCRIPTION = {'CX1',CX2".....}), 'Other Customet Type),

CREDITCODE = {YES}>} AMOUNT )

[vishsaggi]

OK can you share your sample app your are working on. You need to differentiate to which Group this CustomerType should be linked. Just in your Load script you have to add this IF statement.

[jadams123]

Not sure I can I share...QV is on my work computer

[vishsaggi]

Can you post some sample data onto excel and share. With your expected output.

[Digvijay_Singh]

Not sure but this may fit in to find the solution at front end only -

It looks like you want to create synthetic dimension from your current customer type dimension. If you are sure that you would have only 10 final dimension values in your output (CX1..4, CX5..8, 'Other CX Type EAST' and Other CX Type WEST) then you can use valuelist() to create synthetic dimension.

Dimension -

=Valuelist('CX1','CX2','CX3','CX4','Other CX Type EAST','CX5','CX6','CX7','CX8','Other CX Type WEST')

Expression -

Pick(Match(Valuelist('CX1','CX2','CX3','CX4','Other CX Type EAST','CX5','CX6','CX7','CX8','Other CX Type WEST'),

  'CX1','CX2','CX3','CX4','Other CX Type EAST','CX5','CX6','CX7','CX8','Other CX Type WEST'),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX1'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX2'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX3'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX4'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {"=if(Match(CUSTOMER_TYPE,'CX1','CX2','CX3','CX4')=0 and Group='EAST'"}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX6'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX7'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX8'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {'CX9'}, CREDIT_CODE = {'YES'}>} AMOUNT ),

   Sum({$<GLYEAR={2017},CUSTOMER_TYPE = {"=if(Match(CUSTOMER_TYPE,'CX5','CX6','CX7','CX8')=0 and Group='WEST'"}, CREDIT_CODE = {'YES'}>} AMOUNT ))

You would need to format set expression to make them workable.

[jadams123]

This is what I am trying to do...

data:

Year	Customer Code	Customer Type	Sales	Customer Type2
2017	COP	DIS	$100.00	DIS
2017	COP	IDI	$101.00	IDI
2017	COP	IFR	$102.00	IFR
2017	COP	IGT	$103.00	COP Other
2017	COP	INI	$104.00	COP Other
2017	COP	ITC	$105.00	COP Other
2017	COP	KCN	$106.00	COP Other
2017	COP	RTH	$107.00	COP Other
2017	COP	SAM	$108.00	COP Other
2017	COP	STP	$109.00	COP Other
2017	EST	AMB	$110.00	AMB
2017	EST	AME	$111.00	AME
2017	EST	AMP	$112.00	AMP
2017	EST	AVR	$113.00	AVR
2017	EST	BCT	$114.00	BCT
2017	EST	BED	$115.00	BED
2017	EST	BLB	$116.00	BLB
2017	EST	BRI	$117.00	BRI
2017	EST	CFF	$118.00	EST Other
2017	EST	CHN	$119.00	EST Other
2017	EST	CRP	$120.00	EST Other
2017	EST	CRS	$121.00	EST Other
2017	EST	DIS	$122.00	EST Other
2017	EST	EPQ	$123.00	EST Other
2017	EST	FRR	$124.00	EST Other
2017	EST	FRM	$125.00	EST Other
2017	EST	FST	$126.00	EST Other
2017	EST	GRM	$127.00	EST Other
2017	EST	GTG	$128.00	EST Other
2017	EST	HIF	$129.00	EST Other
2017	EST	HPT	$130.00	EST Other
2017	EST	IBU	$131.00	EST Other
2017	WST	ASG	$163.00	ASG
2017	WST	ASK	$164.00	ASK
2017	WST	BLL	$165.00	BLL
2017	WST	BLR	$166.00	BLR
2017	WST	CHR	$167.00	CHR
2017	WST	CMP	$168.00	CMP
2017	WST	CRM	$169.00	CRM
2017	WST	DAD	$170.00	WST Other
2017	WST	DIS	$171.00	WST Other
2017	WST	DRN	$172.00	WST Other
2017	WST	EGS	$173.00	WST Other
2017	WST	FAM	$174.00	WST Other
2017	WST	FED	$175.00	WST Other
2017	WST	FRM	$176.00	WST Other
2017	WST	GRM	$177.00	WST Other
2017	WST	GTG	$178.00	WST Other

pivot

Sum of Sales
Customer Code	Customer Type2	Total
COP	COP Other	$742.00
	DIS	$100.00
	IDI	$101.00
	IFR	$102.00
COP Total		$1,045.00
EST	AMB	$110.00
	AME	$111.00
	AMP	$112.00
	AVR	$113.00
	BCT	$114.00
	BED	$115.00
	BLB	$116.00
	BRI	$117.00
	EST Other	$1,743.00
EST Total		$2,651.00
WST	ASG	$163.00
	ASK	$164.00
	BLL	$165.00
	BLR	$166.00
	CHR	$167.00
	CMP	$168.00
	CRM	$169.00
	WST Other	$1,566.00
WST Total		$2,728.00
(blank)	(blank)
(blank) Total
Grand Total		$6,424.00

[Anil_Babu_Samineni]

Like this?

[vishsaggi]

Check this? I copied your table data onto Excel sheet. What is your data source is that Excel or some other data bases?

Table1:

LOAD [Customer Code],

    [Customer Type],

    [Customer Type2],

    SalesField,

    Year

FROM

[..\Desktop\Sample1.xls]

(biff, embedded labels, table is Sheet1$);