Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Hi Neville,
Well, i understand that first table has the information that you need, like BUD_CUM and ACT_CUM. So i just Sum() to get the total of values of them.But if you don use both just delete the SUM() function of it.
About the second table, if i has understand rightly you used a left join() on Final table. So i believe that output will bring all that you need, A total of BUD_CUMUMATIVE and ACT_CUMULATIVE where BUD_CUMULATIVE are < that your budget.
Any body please refer this & help me!
Rgds
Neville
Hey Neville,
Why you don't do this on script?
Dear Eduardo,
Thanks, please do write it n the script. Please do it so that I can understand it clearly.
Neville
Aux:
LOAD
BR_CODE,
CATEGORY,
NOTE_CODE,
TYPE,
ACT_MONTH,
BUD_MONTH,
F7,
SUM(ACT_CUM) AS ACT_CUMULATIVE,
SUM(BUD_CUM) AS BUD_CUMULATIVE,
F10
FROM
GROUP BY
BR_CODE,
CATEGORY,
NOTE_CODE,
TYPE,
ACT_MONTH,
BUD_MONTH,
F7,
F10
;
NoConcatenate
Final:
Load
*
resident Aux
where BUD_CUMULATIVE< YOUR BUD
DROP TABLE Aux
Dear Eduardo,
Below being the 2nd file I Joined (left) to the 1st one which you have modified in the script. I am thankful to you if you explain me how this file is loaded which is not appeared in your script. Also the below renaming used by you is because in the formula written above carries the 2nd terming or why? Now I have all expression as ACT-CUM & BUD-CUM. In that case if I do not do the renaming & go a head as you done is it alright. Please do incorporate all the changes & all the tables to be loaded & write down the script again if I do not trouble!
Thank you very much.
SUM(ACT_CUM) AS ACT_CUMULATIVE,
SUM(BUD_CUM) AS BUD_CUMULATIVE,
LOAD
BR_CODE ,
CATEGORY ,
NOTE_CODE ,
TYPE ,
REASONS
FROM
(biff, embedded labels, table is Sheet1$);
Hi Neville,
Well, i understand that first table has the information that you need, like BUD_CUM and ACT_CUM. So i just Sum() to get the total of values of them.But if you don use both just delete the SUM() function of it.
About the second table, if i has understand rightly you used a left join() on Final table. So i believe that output will bring all that you need, A total of BUD_CUMUMATIVE and ACT_CUMULATIVE where BUD_CUMULATIVE are < that your budget.