topic Re: Last 12 months Avg in QlikView

Last 12 months Avg

bharatkishore — Mon, 30 May 2016 07:18:41 GMT

Hi All,

I have field called MonthYear and the the data is in Text format and i will be receiving the data from excel where the data will in this format

May'15

Jun'15

Jul'15

.....(till) May'16

I have a field called Distributed Leads where the data is 17,32,89

Now i need calculate the last 12 months avg...

so that should be from May'15 to Apr'16.

Can you please help me how to achieve this

Thanks,

Bharat

kamal_sanguri — Mon, 30 May 2016 07:39:56 GMT

Could you please elaborate it more.. or if possible attach some sample data..

jonathandienst — Mon, 30 May 2016 07:52:43 GMT

The first step will be to load the dates as numeric Qlikview date values. You can use a load statement like:

LOAD

...

Date#(PurgeChar(MonthYear, chr(39)), 'MMMyyyy') as MonthYear,

...

(assumes that he apostrophe in the date is ' (chr(39)))

For the rest of your requirements, I suggest that you attach a qvw sample file to get accurate and useful suggestions.

jonathandienst — Mon, 30 May 2016 07:54:49 GMT

bharatkishore — Mon, 30 May 2016 07:55:05 GMT

Hi Kamal,

Please find attached excel

kamal_sanguri — Mon, 30 May 2016 08:20:40 GMT

Added few rows for Feb'15 and created chart to show average.. pls see if this is what your were looking for..

-Kamal