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

[#1]

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.

[#2]

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.

[#3]

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

[#4]

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.

;

[#5]

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.

[#6 · Accepted solution]

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

[#7]

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

Thanks.