topic Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year) in QlikView

Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year)

Wed, 06 Jun 2012 16:53:31 GMT

Hello all,

I have a problem to show a report in pivot table. Please check attached excel file. This is a sample report that I exported from my qvw. You can see when I select Year = 2011 and Month = Nov, the report will automatic compare with last year in same month (Using Year and Month as Dimension).

My expression is :

sum({<Year={$(=max(Year)),$(=max(Year)-1)}>}Qty) --- For sum Qty

sum({<Year={$(=max(Year)),$(=max(Year)-1)} >}Sales) --- For sum Sales

For every month I have a table that store which region I want to show in my report. For Example:

Nov 2010 = Region 001,002,003,004,005

Nov 2011 = I just want to show Region 001,003,005

The problem is how to set dimension data according to max month year data? So for sample excel file I attached, I just need to preview region 001,003,005.

Anyone know how to solve it? Thanks before.

Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year)

Wed, 06 Jun 2012 17:59:52 GMT

As I understood, you want to see data based on the latest month and year. In this case, I would load latest month and year in the script first, and then load previous year data left join on month.

Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year)

Wed, 06 Jun 2012 18:08:35 GMT

thanks max for your response... can you give me a example for your script?

Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year)

Wed, 06 Jun 2012 19:12:24 GMT

Sure, try something like this:

Table_A:

load

Region,

Month,

Year,

NettoSales,

TotalBill

From

file_name

;

Table_B:

NoConcatenate load //avoid synthetic joins

Region,

Month,

Year as Year_B,

NettoSales,

TotalBill

From

file_name //same or different file

;

Left join (Table_A)

load *

Resident

Table_B

Where

Year > Year_B //only the same months will be joined for previos years.

;

Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year)

Wed, 06 Jun 2012 19:24:54 GMT

oops, last join should look like this:

Left join (Table_A)

load

Region,

Month,

Year_B as Year

NettoSales,

TotalBill

Resident

Table_B

Where

Year > Year_B //only the same months will be joined for previos years.

;

Drop table Table_B;

Now it is better.

Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year)

Thu, 07 Jun 2012 15:00:19 GMT

thanks max! I think your solution will work in case the transaction and region data (what region to be show data) in one table. My situation now is I have one table full of transaction data and one table contain data which region to be included in report (Field : Year, Month, Region). So, for effective way, I add field Flag in table Region then remove Year & Month dimension and change my expression by using IF condition in expression of my pivot table.

The expression become like this:

Sales current year:

sum({<Year={<$(=max(Year))>},FlagR = {1}>}Sales)

Qty current year:

sum({<Year={<$(=max(Year))>},FlagR = {1}>}Qty)

Sales last year:

if(Column(1)=0,0, sum({<Year={<$(=max(Year)-1)>},FlagR = {1}>}Sales))

Qty last year:

if(Column(1)=0,0, sum({<Year={<$(=max(Year)-1)>},FlagR = {1}>}Qty))

In pivot-table properties->Presentation->allow 'Supress Zero-Values'

And the result will become what I want. Only contain 3 regions. At last thank you to Max for your response.

Regards

Urgent.. Hide Row in Pivot Table (Set Dimension Data in Pivot Table According Selected Month & Year)

Thu, 07 Jun 2012 15:31:59 GMT

Heri, welcome. I like you solution. Very neat.

Thanks.