Dear Frank, or any of our dear ones!
I have shown below two tables loaded to a straight table.1st one refers to data extracted from expenses table which has almost 4 dimensions. I have made the variance between ACT_CUM Vs BUD_CUM through an expression.2nd table created for detailed reasons for whatever adverse variance in the 1st table (Note: Adverse variance happens when ACT goes over Budget (ACT>BUD)). My requirement is as follows:
1: To link reasons for expenses which takes negative variance! (This requirement I have already achieved)
2:I need to exclude all the records(expenditure items) which are under budget from being shown.
I request either Mr Frank or any body to suggest me an expression to achieve this. Mr Frank once proposed me an expression on this that was when there was a field for variance. But in the present scenario variance is an expression. Your suggested expression too is noted below. Appreciate, if this is looked in to by any body. The formula I applied too given at the very bottom which is partly ok which only link reasons to expenses which takes a negative value leaving other items (positive) being appeared. I only need to hide those records as I no longer required them for further analysis!
Regards
Neville!
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)
LOAD
BR_CODE,
CATEGORY,
NOTE_CODE,
TYPE,
ACT_MONTH,
BUD_MONTH,
F7,
ACT_CUM,
BUD_CUM,
F10
FROM (biff, embedded labels, table is [RA(EXP)$]);
LOAD
BR_CODE AS BR_CODE1,
CATEGORY AS CATERGORY1,
NOTE_CODE ,
TYPE AS STYPE1,
REASONS
FROM
(biff, embedded labels, table is Sheet1$);
SUM({<ACT_CUM={"=SUM(AGGR(SUM(ACT_CUM),CATEGORY,BR_CODE,TYPE,NOTE_CODE))>SUM(AGGR(SUM(BUD_CUM),CATEGORY,BR_CODE,TYPE,NOTE_CODE))"}>}ACT_CUM)
