How to skip Current Month sales in Pivot Table

[SatyaPaleti]

Hi Folks,

I have a requirement. Can some one help on this. I am having Dimensions Month and Metrics Sales. I want to show all the monthly sales except current month

I want to show only July and August Sales.

and Current Month i.e September Month should show all the dates available in September. Like below Report

Please help on this. For refernce please find attached qvw and sample data

Thanks and Regards,

Satya

[YoussefBelloum]

Hi,

Remove the second dimension and put this as the first dimension:

=if(Month = 'Sep',Date(Date),Month)

[SatyaPaleti]

Hi  youssef,

Thank you so much for your reply but I need Dynamically. it shoud not be hardcoded. Current month is Sep. But if we goes to October then it will be error

Thankd and Regards,

Satya

[vamsee]

Modified you expression

sum({<Month-={"$(=Month(Today()))"} >}Sales)

[SatyaPaleti]

Hi Vamshee,

Thank you it works.. Can you please help me with second senario

Thanks and Regards,

Satya

[MK9885]

If you can create a field in back end. Better to create Dates, Month, Year etc from your Sales table but not master tables since you cannot use Month (Today()) if using Master Calendar.

Num(Month(Date)) as Sales_NumMonth

and in front end you can use for 1st chart

sum({<Sales_NumMonth -= {$(=Max(Sales_NumMonth))}, Categories ={'IFS'} >}Sales)

sum({<Sales_NumMonth -= {$(=Max(Sales_NumMonth))}, Categories ={'VFS'} >}Sales)

sum({<Sales_NumMonth -= {$(=Max(Sales_NumMonth))}, Categories ={'OTHERS'} >}Sales)

For 2nd Chart

sum({<Sales_NumMonth  = {$(=Max(Sales_NumMonth))}, Categories ={'IFS'} >}Sales)

sum({<Sales_NumMonth  = {$(=Max(Sales_NumMonth))}, Categories ={'VFS'} >}Sales)

sum({<Sales_NumMonth  = {$(=Max(Sales_NumMonth))}, Categories ={'OTHERS'} >}Sales)

[nevilledhamsiri]

Use Set Expression!

SUM({<MONTH={"=MONTH<>'SEP' "}>} SALES)

Neville

[vamsee]

for the second chart use date as dimension and expression

sum({<Month={"$(=Month(Today()))"} >}Sales)