Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All!
Please refer P/T made based on the excel data give below. Expression used for getting the averages are tow given below. But this is not the out put I need. I need only latest three Months average to be considered. I have figures for 4 months. Help me to consider average of ( Oct down to the Aug) to be taken.
Regds
Neville
AVG(AGGR(SUM(NRP),BRANCH,MONTH))
BRANCH | MONTH | NRP | COMM | EXPENSES |
RA | JUL | 500,000 | 200,000 | 50,000 |
RA | AUG | 650,000 | 150,000 | 75,000 |
RA | SEP | 500,000 | 125,000 | 125,000 |
RA | OCT | 450,000 | 100,000 | 100,000 |
EH | JUL | 400,000 | 45,000 | 75,000 |
EH | AUG | 450,000 | 55,000 | 125,000 |
EH | SEP | 250,000 | 25,000 | 125,000 |
EH | OCT | 325,000 | 50,000 | 150,000 |
Try to reply over my conversation only rather yours. And here you go
Please help me on this!
Thanks
Neville
Is this you are going to see?
No,
Anil, I need the average of latest three Months (AUG+SEP+OCT)
Thanks
Neville
Expected result?
EH - 14,25,000
RA - 21,00,000
No,
Actually I need to have average of latest three Months. The figures should be like below
NRP COMM EXPENSES PROFIT
RA 533 125 100 308
EH 341 43 133 165
Thanks
Neville
PFA
Dear Anil,
Please send me the expression used!
Neville
Try to reply over my conversation only rather yours. And here you go
Your solution did work!. But since I am not that famillar with the month presentation used such as below & please explain followings! This is for mr to grasp this more.
What is meant by "FLOOR'
What is 1e3 mean , Is is 1/3 we generally used.
Num_Month refers to Number of Months or otherwise
Is there any other simple way the latest average may be taken.
While thanking you, I hope you to explain on these terms used & if possible please suggest a lighter way of calculating this which will be of important.
Neville
Num(month(date#(MONTH,'MMM'))) as Num_Month Inline
Floor(Avg({<Num_Month = {">$(=Max(Num_Month-3)) <=$(=Max(Num_Month))"}>} NRP)/1e3)