Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
satyapaleti
Contributor II

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
YoussefBelloum
Esteemed Contributor

Re: How to skip Current Month sales in Pivot Table

Hi,

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

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

satyapaleti
Contributor II

Re: How to skip Current Month sales in Pivot Table

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
Valued Contributor

Re: How to skip Current Month sales in Pivot Table

Modified you expression

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

satyapaleti
Contributor II

Re: How to skip Current Month sales in Pivot Table

Hi Vamshee,

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

Thanks and Regards,

Satya

Khan_Mohammed
Honored Contributor II

Re: How to skip Current Month sales in Pivot Table

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
Valued Contributor

Re: How to skip Current Month sales in Pivot Table

Use Set Expression!

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

Neville

vamsee
Valued Contributor

Re: How to skip Current Month sales in Pivot Table

for the second chart use date as dimension and expression

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