Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

Always to take in to account latest three Months Avearge!

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))

   

BRANCHMONTH NRP COMMEXPENSES
RAJUL         500,000          200,000            50,000
RAAUG         650,000          150,000            75,000
RASEP         500,000          125,000          125,000
RAOCT         450,000          100,000          100,000
EHJUL         400,000            45,000            75,000
EHAUG         450,000            55,000          125,000
EHSEP         250,000            25,000          125,000
EHOCT         325,000            50,000          150,000
14 Replies
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 ..

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nevilledhamsiri
Specialist
Specialist
Author

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nevilledhamsiri
Specialist
Specialist
Author

Thank you very much!

Anil_Babu_Samineni

Glad, You liked it !!

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)