Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
mohsinqlik
Partner - Creator
Partner - Creator

Show data 6 month back from current month

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.

jaganswuehltresescoAvinashelite

15 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
jagan
Luminary Alumni
Luminary Alumni

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

Hi Gysbert,

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

jagan
Luminary Alumni
Luminary Alumni

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?


talk is cheap, supply exceeds demand
mohsinqlik
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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.


talk is cheap, supply exceeds demand