Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Maybe something like that:
sum({<ACT_CUMULATIVE={'>$(=BUD_CUMULATIVE)'}>}ACT_CUMULATIVE)
hope this helps
Dear Frank,
Once applied it answer wont come. See the expression & chart below!
SUM({<ACT_CUMULATIVE={'>$(=BUD_CUMULATIVE)'}>}ACT_CUMULATIVE)
Thanks
Neville
try this:
SUM({< ACT_CUMULATIVE = {"=ACT_CUMULATIVE > BUD_CUMULATIVE"}>}ACT_CUMULATIVE)
OR:
SUM({< ACT_CUMULATIVE = {"=sum(aggr(sum(ACT_CUMULATIVE),CATEGORY,BR_CODE)) > sum(aggr(sum(BUD_CUMULATIVE),CATEGORY,BR_CODE))"}>} ACT_CUMULATIVE)
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
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)
Dear Frank,
That produce the former results but what is not negative yet to be there without being excluded!
Thanks
Neville
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
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