Show data 6 month back from current month

[mohsinqlik]

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‌

[Gysbert_Wassenaar]

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

[jagan]

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.

[mohsinqlik]

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

[mohsinqlik]

Hi Gysbert,

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

[jagan]

Hi,

Try this expression

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

Regards,

Jagan.

[Gysbert_Wassenaar]

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

[mohsinqlik]

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.

[mohsinqlik]

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

[Gysbert_Wassenaar]

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.