topic Re: Last 6 months data in QlikView

Last 6 months data

Anonymous — Fri, 23 Dec 2016 10:36:30 GMT

Dear All

I have a requirement where I need to calculate average of last 6 months sales.

Example: If we are in December we need to calculate average from June to November..

In Jan, from July to Dec and so on. Kindly help to achieve this possibility

Thanks & Regards

Chintan Gala

Re: Last 6 months data

veeranj — Fri, 23 Dec 2016 10:42:37 GMT

Hi Chintan,

You can use Rangesum function like rangesum(sum(sales),0,6) where as Month is your dimension .

Thanks,

Veera

Re: Last 6 months data

PrashantSangle — Fri, 23 Dec 2016 10:58:07 GMT

Hi,

Use rangeavg()

Regards

Re: Last 6 months data

Anonymous — Fri, 23 Dec 2016 11:21:46 GMT

Or is it better to show running average by taking last 180 days from today?

Thanks & Regards

Chintan Gala

Re: Last 6 months data

PrashantSangle — Fri, 23 Dec 2016 11:29:49 GMT

not necessary 6 months = 180 days

Regards,

Re: Last 6 months data

vinieme12 — Fri, 23 Dec 2016 12:13:58 GMT

where are you trying to show this info Textbox? Straight Table (Rolling Average) ? or script?

create a YearMonth field in your data table to simplify the calculations

example: 201601,201602 and so on

and vStartYearMonth = NUM(year(Today())) & NUM(month(Today()),'00')

and vEndYearMonth = Addmonths( Date($(vStartYearMonth),'YYYYMM'),-6)

Just ensure your Month variables are '00' format meaning Jan is '01' and not '1'

You expression should then be

SUM ( {< YearMonth = {">=$(vStartYearMonth)<$(vEndYearMonth)"} >} Sales)

with YearMonth you will be compare months over different year example 201611 to 201704

Re: Last 6 months data

Anonymous — Tue, 03 Jan 2017 05:22:14 GMT

Thanks Vineeth. I will try the same and let you know.

I am trying to do this in front end, probably pivot table or straight table.

Thanks & Regards

Chintan Gala

Re: Last 6 months data

Anonymous — Tue, 03 Jan 2017 10:02:14 GMT

Dear Vineeth

The vEndYearMonth variable is not giving 6 month previous YearMonth. It gives a junk date as 26-09-2654 something. Can you please suggest if any modification is required?

Thanks & Regards

Chintan Gala

Re: Last 6 months data

parimikittu — Tue, 03 Jan 2017 11:32:33 GMT

Hi Chintan, It is always better to create a numeric value(extra column) for each month for whole master calendar. for example month,year,id This id has a numeric value. Month_numeric: Load Distinct Month, Year, Rowno()+100 as Mon_num resident MasterCalendar Join the above with Master Calendar. SUM ( {< Month = {"=$(=Max(Mon_num)-7)"} >} Sales)

Re: Last 6 months data

Anonymous — Tue, 03 Jan 2017 12:45:48 GMT

Dear Vj

Thanks for your reply. I will try the same and let u know if I am able to achieve the required result.

Thanks & Regards

Chintan Gala

Re: Last 6 months data

vinieme12 — Tue, 03 Jan 2017 13:49:47 GMT

Sorry try

and vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth)),-6),'YYYYMM')

Re: Last 6 months data

bobbydave — Tue, 03 Jan 2017 14:37:50 GMT

I had the same issue only a few weeks back with an application i was working on

In your script

LOAD Date(Date#([TheYear Month]), 'YYYY-MM'), 'YYYY-MM') as [The Year Month]

Your expression

Avg({$<[The Year Month] = {">=$(=Date(AddMonths(Max(Today()),-6), 'YYYY-MM'))<=$(=Date(AddMonths(Max(Today()),-1), 'YYYY-MM'))"}>} [Your Value to Measure])

The expression will always calcalate the date between the last 6 months and last month always so for this month, you will get Dec last month and 6 months previous. Next Month, you will get Jan and 6 months previous. Make sure to declare [The Year Month] in your load script.

Re: Last 6 months data

luizcdepaula — Wed, 04 Jan 2017 15:14:57 GMT

Hi David,

What worked for me was a little different but very similar. The main thing that I changed was to replace the [The Year Month] dimension to the actual [Date] dimension.

Added to load script:

LOAD Date([Date]), 'YYYY-MM'), as [The Year Month]

Calculation expression in app:

Avg({$<[The Year Month]={">$(=Date(AddMonths(Today(),-6),'YYYY-MM'))<=$(=Date(AddMonths(Today(),-1),'YYYY-MM'))"}>}[Your Value to Measure])

It is working and calculating previous 6 months.

Thanks,

Re: Last 6 months data

bobbydave — Thu, 05 Jan 2017 09:02:52 GMT

Hi Luiz,

Delighted it worked. Took me ages to get that working also.

Please mark as correct.

Re: Last 6 months data

Anonymous — Thu, 05 Jan 2017 12:49:52 GMT

Hello Luiz

As you are using today()-6 , I guess it will go to previous 6 months from today. This would not work if we select any previous month as filter.

Thanks & Regards

Chintan Gala

Re: Last 6 months data

luizcdepaula — Thu, 05 Jan 2017 14:41:00 GMT

Hello Chintan,

You would have to add an IF condition then.

Ex:

If( (GetSelectedCount (MonthYear) = 0 and GetSelectedCount (Year) = 0),

Avg({$<[The Year Month] = {">=$(=Date(AddMonths(Today(),-6), 'YYYY-MM'))<$(=Date(AddMonths(Today(),0), 'YYYY-MM'))"}>} [Your Value to Measure]),

Avg([Your Value to Measure]))

This expression will give you either last 6 finished full months if no date as to MonthYear or Year is selected, or the AVG of whatever months you select.

If( (GetSelectedCount (MonthYear) = 0 and GetSelectedCount (Year) = 0),

Avg({ $ <[The Year Month] = {">=$(=Date(AddMonths(Today(),-6), 'YYYY-MM'))<$(=Date(AddMonths(Today(),0), 'YYYY-MM'))"}>} [Your Value to Measure]),

Avg({ 1 <[The Year Month] = {">=$(=Date(AddMonths(Max(Date),-6), 'YYYY-MM'))<$(=Date(AddMonths(Max(Date),0), 'YYYY-MM'))"}>} [Your Value to Measure]))

This expression will give you either last 6 finished full months if no date as to MonthYear or Year is selected, or the AVG of last 6 months from the MonthYear you select. Make sure you use 1 instead of $ on the second part of the expression.

Cheers,

Re: Last 6 months data

Anonymous — Tue, 10 Jan 2017 10:53:23 GMT

Dear Vineeth

I am unable to create the previous 6 months from

vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth)).,-6),'YYYYMM')

Is there any other way around?

Regards

Chintan

Re: Last 6 months data

vinieme12 — Tue, 10 Jan 2017 12:57:58 GMT

Date(Addmonths( Date($(vStartYearMonth),'YYYYMM'),-6),'YYYYMM')

There was a full stop before comma .,

Re: Last 6 months data

neelamsaroha157 — Tue, 10 Jan 2017 14:26:56 GMT

Try this

Avg({$<Date={"<$(=MonthEnd(AddMonths(Max(Date),-1))>$(=MonthStart(AddMonths(MonthEnd(AddMonths(Max(Date),-1)), -6)))"}>}Value)

Re: Last 6 months data

Anonymous — Wed, 11 Jan 2017 05:31:26 GMT

It shows expression OK, but later shows error as ')' expected.