Always to take in to account latest three Months Avearge!

[nevilledhamsiri]

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

[Anil_Babu_Samineni]

Looks like all are self explanation only.

1) Floor() Vs Ceil() -- where we use floor() and ceil() in qlikview?

2) 1e3 means i am just deviding to 1000 because of your expected result

3) Num_Month is used for number digits of month like (Jan-1, feb-2, Mar-3,...Dec-12)

Num(month(date#(MONTH,'MMM'))) as Num_Month Inline

- This expression trick for changing from month names to month numbers for set analysis to get into work instead month names

Floor(Avg({<Num_Month = {">$(=Max(Num_Month-3)) <=$(=Max(Num_Month))"}>} NRP)/1e3)

And, Looks like this expression deserves for last and/or latest three months of Profit Average and then If you read clear on Floor() you came to know about that.

Does this make sense ..

[nevilledhamsiri]

Of course thanks,

Without using inline feature, if I load the excel as a separate data file, then how & at what place below expression that is to convert Months names in to Month numbers be placed.

Num(month(date#(MONTH,'MMM'))) as Num_Month Inline

[Anil_Babu_Samineni]

I replied earlier, Try to reply same conversation only even in future

For your question, Simply add any load statement of field like

Load Field1,

          Field2,

          Field3,

          MONTH, 

          Num(month(date#(MONTH,'MMM'))) as Num_Month

From Excel_Table;

[nevilledhamsiri]

Thank you very much!

[Anil_Babu_Samineni]

Glad, You liked it !!