Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

How to skip Current Month sales in Pivot Table

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

ytd.JPG

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

ytd.JPG

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

Thanks and Regards,

Satya

7 Replies
Highlighted
Champion
Champion

Hi,

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

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

Highlighted
Creator III
Creator III

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

Highlighted
Specialist
Specialist

Modified you expression

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

Highlighted
Creator III
Creator III

Hi Vamshee,

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

Thanks and Regards,

Satya

Highlighted
Master II
Master II

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)

Highlighted
Specialist
Specialist

Use Set Expression!

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

Neville

Highlighted
Specialist
Specialist

for the second chart use date as dimension and expression

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