topic Re: Limit Dates shown in pivot table in QlikView

Limit Dates shown in pivot table

Tue, 02 Feb 2016 14:58:55 GMT

I'm working on creating a trending report for COGS% in a pivot table. The report has our customers listed down on the left side with the columns being the our fiscal months. I have three years worth of data however and only want the pivot table to show the most recent 7 months at any given point in time. I've been told I need to do a set analysis but am unsure of how to incorporate that in to my sum statement.

Here is the current sum statement: (Sum({<[Trending]={'Cost of Sales'}>} [Amount]) / sum({<[Trending]={'Revenue'}>} [Amount]))

My date field is named MonthYr and is in the format of mm-dd-yyyy. Ideally, I want to set up a formula where if 1/31/2016 is the most recent date for MonthYr variable within the data set, that the columns shown in my pivot table are Jan-16, Dec-15, Nov-15, Oct-15, Sep-15, Aug-15, Jul-15.

Thank you in advance for any help provided.

Re: Limit Dates shown in pivot table

MK_QSL — Tue, 02 Feb 2016 17:42:58 GMT

Provide sample data please !

Re: Limit Dates shown in pivot table

Gysbert_Wassenaar — Tue, 02 Feb 2016 17:49:14 GMT

Something like this:

Sum({<[MonthYr={">=$(=MonthStart(Max(MonthYr),-6))<=$(=Date(Max(Date)))"}, Trending]={'Cost of Sales'}>} [Amount]) / sum({<[MonthYr={">=$(=MonthStart(Max(MonthYr),-6))<=$(=Date(Max(Date)))"}, [Trending]={'Revenue'}>} [Amount])

Re: Limit Dates shown in pivot table

ajayrai — Tue, 02 Feb 2016 18:00:03 GMT

Create a variable (vPrevSevenMonths) which will contain the 7 months back date and use it in set analysis.

vPrevSevenMonths=MonthStart(AddMonths(max(Month),-7))

Like this.

=Sum({<Date ={"<=$(=vPrevSevenMonths)"}>} FieldName)

Re: Limit Dates shown in pivot table

Thu, 04 Feb 2016 13:51:39 GMT

I've tried both the methods which have been mentioned above by Gysbert and Ajay to no avail. As I was unsure if it was related to the various linkage between my main tables, I removed all tables from my query except for 1 which contains InvoiceDate, Amount, and Unit. Applying the methods above to the field InvoiceDate (as opposed to the previously mentioned MonthYr field) still does not produce any results. My InvoiceDate field is being loaded in the format of MM/DD/YYYY Hr:Mm:Ss AM; could it be this load format which is causing the issue?

Re: Limit Dates shown in pivot table

Gysbert_Wassenaar — Thu, 04 Feb 2016 17:51:03 GMT

Could be. Perhaps your MonthYr field contains text values. Try posting a small qlikview document that demonstrates the problem.

Re: Limit Dates shown in pivot table

ajayrai — Thu, 04 Feb 2016 17:53:10 GMT

Date format should be same on both sides which you are comparing.

Post a sample application or sample excel which contains your dummy data.