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]

Will some one look in to this!.

Thanks

Neville

[nevilledhamsiri]

Dear Sunny, Stefan,Anil,Oscar & the like, 

Please be kind enough to look in to this.This is very urgent! Sorry for bothering you all.

Rgds

Neville

[nevilledhamsiri]

Dear Oscar,

At least will you be able to look in to this. So far no one has responded

Thanks

Neville

[nevilledhamsiri]

Please help me to achieve my objective!

Simply I need to link the two table & then pick the reason from the 2nd file to be appear before the expense which has taken negative value.

Thanks

Neville

[vishsaggi]

What is your expected output out of these two tables? when you join these two there is a link formed automatically on the Field TYPE. Is it not how you see in your data model? And your expression

SUM({<BR_CODE={'RA'},TYPE={'STAFF SALARIES'}>}REASON_1) what are you trying to sum here you will always get 0 as REASON_1 has text and you are summing it.

[nevilledhamsiri]

Dear Vishwarath,

Yes What You say is right. Will you help on this.

My requirement is once I loaded the 1st table which carries some expressions & once I arrive at the Variance & when it is negative what I need is to pick the suited reason from the 2nd table which carries only dimensions. Eg If I am to show you If the staff salaries are negative in variance I need to write an expression to pick reason 1  or 1 & 3 etc to be appeared against that expenses. Like wise for all expenses I need to pick from that table.

Simply show me an expression to pick a text from one table to be shown in a another table against a particular field such as what I have shown above.

Thanks

Neville

[vishsaggi]

How do you know which reason to pick? I mean what is the criteria here?

[nevilledhamsiri]

Only Standard reasons were written as 1,2,3 & 4 etc. Based on the Branch, either 1st , 2nd or 3rd  reasons to be chosen. There is no direct criteria as such. If I expose my whole case which looks like below!

When the Management expenses are getting a negative variance, I need to analyze them with data such as actual , budget & variance .Thereafter to produce reasons. I thought of arranging the figures in the 1st table & standard reasons against each expenses in the 2nd table & write an expression to pick either 1st, 2nd or 1st+2nd reason etc. You may propose some other option more productive than this if available.

Just show me the possible expression to use to pick said reasons

Thanks

Neville

[vishsaggi]

Sorry My friend, i am not quite getting what you want. So let me get more understanding of this. When you have a variance negative like example

-299998,33 for this Variance what is your expected reason you are expecting ?