Can the set Expression be used to show somthing > or <

[nevilledhamsiri]

Hi,

As per the data presented below, could you show me how records of which  ACT_CUM>BUD_CUM   be shown using Set expression taking in to account the related dimensions! related data are as follows!

Thanks

Neville

BR_CODE	CATEGORY	NOTE_CODE	TYPE	ACT_CUMULATIVE	BUD_CUMULATIVE	VARIANCE
RA	STAFF EXPENSES	500	STAFF SALARIES	7,507,077.41	7,480,400.00	-26,677.41
RA	STAFF EXPENSES	513	STAFF WELFARE	242,390.18	224,000.00	-18,390.18
RA	SELLING EXPENSE	702	BUSINESS PROMOTION	259,798.33	265,000.00	5,201.67
EM	STAFF EXPENSES	500	STAFF SALARIES	8,500,000.00	7,480,400.00	-1,019,600.00
EM	STAFF EXPENSES	513	STAFF WELFARE	350,000.00	500,000.00	150,000.00
EM	SELLING EXPENSE	702	BUSINESS PROMOTION	259,798.33	229,800.00	-29,998.33

[Frank_Hartmann]

Maybe something like that:

sum({<ACT_CUMULATIVE={'>$(=BUD_CUMULATIVE)'}>}ACT_CUMULATIVE)

hope this helps

[nevilledhamsiri]

Dear Frank,

Once applied it answer wont come. See the expression & chart below!

SUM({<ACT_CUMULATIVE={'>$(=BUD_CUMULATIVE)'}>}ACT_CUMULATIVE)

Thanks

Neville

[Frank_Hartmann]

try this:

SUM({< ACT_CUMULATIVE = {"=ACT_CUMULATIVE > BUD_CUMULATIVE"}>}ACT_CUMULATIVE)

[Frank_Hartmann]

OR:

SUM({< ACT_CUMULATIVE = {"=sum(aggr(sum(ACT_CUMULATIVE),CATEGORY,BR_CODE)) > sum(aggr(sum(BUD_CUMULATIVE),CATEGORY,BR_CODE))"}>} ACT_CUMULATIVE)

[nevilledhamsiri]

Dear Frank,

Latest expression did work! Thanks

Neville

Can you also  please propose a suggestion to exclude something being shown in the table. Like what you highlighted the negative variance, is there a way where we could exclude positive records being appeared from the table.

Regrds

Neville

[Frank_Hartmann]

try this:

SUM({< VARIANCE = {"=VARIANCE<0"},ACT_CUMULATIVE = {"=sum(aggr(sum(ACT_CUMULATIVE),CATEGORY,BR_CODE)) > sum(aggr(sum(BUD_CUMULATIVE),CATEGORY,BR_CODE))"}>} ACT_CUMULATIVE)

[nevilledhamsiri]

Dear Frank,

That produce the former results but what is not negative yet to be there without being excluded!

Thanks

Neville

[Frank_Hartmann]

If I correctly understood you then you want to show Result where

ACT_CUMULATIVE > BUD_CUMULATIVE

and the

VARIANCE < 0

If this is not what you want then please show expected Output

[nevilledhamsiri]

Dear Frank,

Please check the negative variance comes as 7507077 seems to be incorrect! Also with all other expression, the last expression written, all rows are yet to be seen!

thnks

Neville