Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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.

1 Solution

Accepted Solutions
Not applicable

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

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

6 Replies
Not applicable

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

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.

Not applicable

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

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

Not applicable

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

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.

;

Not applicable

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

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.

Not applicable

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

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

Not applicable

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

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

Thanks.

Community Browser