How to make appear exact reason againts expenses with negative variance

[nevilledhamsiri]

Hi,

Dear friends!

I have created following table after loading two Excel files as attached below. I need to make appear the reason which I pick up from an expression which too stated below. I have used a set expression to do this but the reason wont come there.For staff salaries of branch RA, I need to bring reason-1 which is "unbudgeted Expense". Also If need to bring reason-1, 2 & 3 before this expense type how it should be done.

Expression used as follows:

SUM({<BR_CODE={'RA'},TYPE={'STAFF SALARIES'}>}REASON_1)

Please see what & where I have gone wrong. You may suggest me a good expression to do this. This is a sample data & same process I need to do for may be around good 40 to 50 records!

Also If I am to  show only expenses type with negative variance excluding positive expenses type, what could be the expression to be used?

EXCEL-1

BR_CODE	CATEGORY	NOTE_CODE	TYPE	ACT_MONTH	BUD_MONTH	VARIANCE_MONTH	ACT_CUMULATIVE	BUD_CUMULATIVE	VARIANCE
RA	STAFF EXPENSES	500	STAFF SALARIES	988,677.41	973,840.00	-14,837.41	7,507,077.41	7,480,400.00	-26,677.41
RA	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	242,390.18	224,000.00	-18,390.18
RA	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	229,800.00	-29,998.33
EM	STAFF EXPENSES	500	STAFF SALARIES	1,000,000.00	973,840.00	-14,837.41	8,500,000.00	7,480,400.00	-26,677.41
EM	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	350,000.00	224,000.00	-18,390.18
EM	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	229,800.00	-29,998.33

EXCEL-2

SORTING_CODE	TYPE	REASON_1	REASON_2	REASON_3	REASON_4	REASON_5
1	STAFF SALARIES	Unbudgeted_Expense	New_Recruits_MES	New_Recruits_Underwriters	New_Recruits_ASM	New_Recruits_accounts
2	STAFF WELFARE	Wedding Gift_UW	Wedding Gift_ME	Wedding Gift_Manager	Tea related Expenses	Staff_Uniform_Peon
3	BUSINESS PROMOTION	Town_Storming	Customer Get together	Finance company cocktal	Donation

Thanks in advance

Good luck

Neville

[nevilledhamsiri]

Dear Tharindu,

Thanks a lot for your response, but the expression seems to be not working!. Have your tried on your own. Please look in to this. If the reasons to b picked just through concat, how the excel table to be changed thereby will not require to develop the said formula with so many addition since I need to link more than 14 Branches over 10 to 15 type of expenses

Neville

[dinuwanbr]

Dear Neville,

only link between these two tables is nothing but the TYPE. But clearly there is no direct relationship for REASONS from BR_CODE and TYPE Combination.

So if you can create a mapping table to create this relationship it will work.

Hope you understand what I say.

Rgds,

Tharindu

[nevilledhamsiri]

Dear Tharindu,Vishwarath

Could you explain me how this mapping table is created! I need  the reasons given in me 2nd excel file to be linked to 1st excel file. If I have given one reason for business promotion of RA then only that reason to be linked to RA under Business promotion!

Thanks neville

[vishsaggi]

Sorry Neville, for this you have to give us more information on how Types are linked to your BRCode. You are saying if BR CODE is EM it should pick reasons1 and Reason2 etc. So, we should know how this BRCode is linked to the Reason. As Rana suggested the should be some kind of mapping to this table. Hows your actual data model looks like? Can you email me we can set up a call for a quick discussion.

Thanks,

V.

[nevilledhamsiri]

Dear Vishwarath,

This is the 1t table which has 4 dimension. 2nd table carries all dimensions. A link can be created through BR code & type since both are present in both table.Could you use your knowledge & think how this could be handled. As you say if a link can e created with Branch vs Reason matter will be solved!

Regds

Neville

BR_CODE	CATEGORY	NOTE_CODE	TYPE	ACT_MONTH	BUD_MONTH	VARIANCE_MONTH	ACT_CUMULATIVE	BUD_CUMULATIVE	VARIANCE
RA	STAFF EXPENSES	500	STAFF SALARIES	988,677.41	973,840.00	-14,837.41	7,507,077.41	7,480,400.00	-26,677.41
RA	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	242,390.18	224,000.00	-18,390.18
RA	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	265,000.00	-29,998.33
EM	STAFF EXPENSES	500	STAFF SALARIES	1,000,000.00	973,840.00	-14,837.41	8,500,000.00	7,480,400.00	-26,677.41
EM	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	350,000.00	500,000.00	-18,390.18
EM	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	229,800.00	-29,998.33
2ND TABLE BR TYPE REASON_1 REASON_2 REASON_3 REASON_4 REASON_5 RA STAFF SALARIES Unbudgeted_Expense New_Recruits_MES New_Recruits_Underwriters New_Recruits_ASM New_Recruits_accounts EM STAFF SALARIES Unbudgeted_Expense New_Recruits_MES RA STAFF WELFARE Wedding Gift_UW Wedding Gift_ME Wedding Gift_Manager Tea related Expenses Staff_Uniform_Peon EM STAFF WELFARE Tea related Expenses RA BUSINESS PROMOTION Town_Storming Customer Get together Finance company cocktal Donation EM BUSINESS PROMOTION Town_Storming

[nevilledhamsiri]

BR_CODE	CATEGORY	NOTE_CODE	TYPE	ACT_MONTH	BUD_MONTH	VARIANCE_MONTH	ACT_CUMULATIVE	BUD_CUMULATIVE	VARIANCE
RA	STAFF EXPENSES	500	STAFF SALARIES	988,677.41	973,840.00	-14,837.41	7,507,077.41	7,480,400.00	-26,677.41
RA	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	242,390.18	224,000.00	-18,390.18
RA	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	265,000.00	-29,998.33
EM	STAFF EXPENSES	500	STAFF SALARIES	1,000,000.00	973,840.00	-14,837.41	8,500,000.00	7,480,400.00	-26,677.41
EM	STAFF EXPENSES	513	STAFF WELFARE	45,159.06	28,000.00	-17,159.06	350,000.00	500,000.00	-18,390.18
EM	SELLING EXPENSE	702	BUSINESS PROMOTION	89,324.79	28,725.00	-60,599.79	259,798.33	229,800.00	-29,998.33

[vishsaggi · Accepted solution]

Try this may be:

Excel1:

LOAD AutoNumberHash128(BR_CODE&TYPE)   AS %BRTYPE_KEY,

     BR_CODE,

     CATEGORY,

     NOTE_CODE,

     TYPE,

     ACT_MONTH,

     BUD_MONTH,

     VARIANCE_MONTH,

     ACT_CUMULATIVE,

     BUD_CUMULATIVE,

     VARIANCE

FROM

[..\Desktop\Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

CrossTable(Reasons, ReasonDesc, 3)

Excel2:

LOAD AutoNumberHash128(BR_CODE&TYPE) AS %BRTYPE_KEY,

     BR_CODE     AS BR,

     TYPE        AS ReasonType,

     REASON_1,

     REASON_2,

     REASON_3,

     REASON_4,

     REASON_5

FROM

[..\Desktop\Book1.xlsx]

(ooxml, embedded labels, table is Sheet2);

Then use pivot table with dim

BAR_CODE, TYPE CATEGORY

Expressions:

= Sum(ACT_CUMULATIVE)

= Sum(BUD_CUMULATIVE)

VARIANCE

= Aggr(Concat(ReasonDesc, '|'), BR_CODE, TYPE,CATEGORY)