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 · Accepted solution]

Sorry, Im not getting you.

please explain why the neg Variance -26677.41 should not be correct?

Both conditions are fullfilled:

the actValue > BudValue

and Variance <0

try this:

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

Maybe you will have to include your specific dimensions into the aggregation function inside set analysis in order to get it work!

See attached File