Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
thanks max for your response... can you give me a example for your script?
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.
;
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.
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
Heri, welcome. I like you solution. Very neat.
Thanks.