topic Re: Show data 6 month back from current month in QlikView

Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 06:44:25 GMT

Dear All,

I have pivot table dimension month, I need to show data as follow :

Jan 2015 should show data from month Jul 2014, Feb 2015 should data from month Aug 2014. Similarly Jul 2015 should show data of Jan 2015 and so on monthwise.

I have used set analysis but it does not work.I have to do it in chart i.e on front end. Kindly share on how do achieve this in front end.

jagan‌swuehl‌tresesco‌Avinashelite‌

Re: Show data 6 month back from current month

Gysbert_Wassenaar — Tue, 04 Aug 2015 08:23:21 GMT

See this document: Calculating rolling n-period totals, averages or other aggregations

Re: Show data 6 month back from current month

jagan — Tue, 04 Aug 2015 08:25:12 GMT

HI,

Try like this

=Sum({<Year=, Quarter=, Month=, Date={'>=$(=MonthStart(Max(Date), -6))<=$(=Date(Max(Date)))'}>} Sales)

Note: Replace Year, Quarter, Month and Date with your actual date fields and Sales with your measure field.

If not working attach some sample file for this.

Regards,

Jagan.

Re: Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 10:14:47 GMT

I have tried above set analysis. As i have month as a dimension max(date) is considered maximum date in year and not of the dimension month.

I am attaching a sample qvw and excel file

Re: Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 10:17:21 GMT

Hi Gysbert,

I need to show data in current month of 6 month back not as rolling month.

Re: Show data 6 month back from current month

jagan — Tue, 04 Aug 2015 10:23:23 GMT

Hi,

Try this expression

RangeSum(Sum(Value), Above(Sum(Value), 1, 6))

Regards,

Jagan.

Re: Show data 6 month back from current month

Gysbert_Wassenaar — Tue, 04 Aug 2015 10:42:00 GMT

See attached qvw. Is that what you're looking for?

Re: Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 10:44:49 GMT

Hi,

I need to show MTD values of 6 month back in current i.e july 2014 data should be shown in jan 2015, aug 2014 data to be shown in feb 2015, sep 2014 to be displayed in mar 2015 and so on .The logic you shared gives rolling data.

Re: Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 10:59:14 GMT

Hi,

Logic you have shared show year of previous year month.It should show Jan - Dec as month and data for each month from 6 month back

Re: Show data 6 month back from current month

Gysbert_Wassenaar — Tue, 04 Aug 2015 11:05:56 GMT

Logic you have shared show year of previous year month.It should show Jan - Dec as month and data for each month from 6 month back

I don't understand what you're saying. I selected jan 2015 and the table shows the data from jul 2014 to jan 2015. As far as I know that's the month and its previous six months data. If that's not what you want then show us the result table you do want. Create an example in excel if you have to.

Re: Show data 6 month back from current month

Kushal_Chawda — Tue, 04 Aug 2015 11:13:49 GMT

You can do something like this

Please see the attached. Slightly modified

Re: Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 11:36:49 GMT

I have attached the output sample

Re: Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 11:39:07 GMT

Hi,

The logic which you have shared is correct but i need to do it in front end.

Re: Show data 6 month back from current month

Kushal_Chawda — Tue, 04 Aug 2015 12:02:13 GMT

ok, try this

=Above(sum(Value),6)

Re: Show data 6 month back from current month

mohsinqlik — Tue, 04 Aug 2015 12:10:40 GMT

Thanks kush . I have used above function .But what i am trying to achieve is through month as a dimension. I am attaching the output file for reference

Re: Show data 6 month back from current month

Kushal_Chawda — Tue, 04 Aug 2015 12:49:19 GMT

Hi,

I think it will be difficult to achieve from front end. may be swuehl or gwassenaar can help you

But you can do this from script.