topic Re: How to skip Current Month sales in Pivot Table in QlikView

How to skip Current Month sales in Pivot Table

SatyaPaleti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to skip Current Month sales in Pivot Table

YoussefBelloum — Mon, 24 Sep 2018 16:02:29 GMT

Hi,

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

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

Re: How to skip Current Month sales in Pivot Table

SatyaPaleti — Mon, 24 Sep 2018 16:21:00 GMT

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

Re: How to skip Current Month sales in Pivot Table

vamsee — Mon, 24 Sep 2018 16:36:48 GMT

Modified you expression

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

Re: How to skip Current Month sales in Pivot Table

SatyaPaleti — Mon, 24 Sep 2018 16:50:25 GMT

Hi Vamshee,

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

Thanks and Regards,

Satya

Re: How to skip Current Month sales in Pivot Table

MK9885 — Mon, 24 Sep 2018 17:07:14 GMT

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)

Re: How to skip Current Month sales in Pivot Table

nevilledhamsiri — Mon, 24 Sep 2018 18:01:47 GMT

Use Set Expression!

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

Neville

Re: How to skip Current Month sales in Pivot Table

vamsee — Mon, 24 Sep 2018 18:03:34 GMT

for the second chart use date as dimension and expression

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