topic Re: How to show current year current month Vs Previous year same month data into pivot table in QlikView

How to show current year current month Vs Previous year same month data into pivot table

venkatbza — Thu, 13 Jul 2017 11:26:49 GMT

Hi Friends,

I am facing a issue to populate the current month sales vs previous year same month sales in pivot table.

I have columns like Year, Month and Sales. and created a pivot table.

if i select any year and month in the list box it should show selected year and month sales and previous year same month sales into the table.

i can populate the current year sales using Sum(Sales) but i am not able to populate the Previous sales.

please help to find the privies sales expression.

below is the out put table if i select the

Year = 2017,2016,2015,2014,2013 and Month = July.

Year	Month	Current year sales	Previous sales
2017	July	5000	6000
2016	July	6000	4500
2015	July	4500	7600
2014	July	7600	3400
2013	July	3400	2000

Re: How to show current year current month Vs Previous year same month data into pivot table

MK_QSL — Thu, 13 Jul 2017 11:37:11 GMT

Dimension

Year

Month

Expression

SUM(Sales)

Below(Total SUM({<Year, Month>}Sales)) * Avg(1)

Re: How to show current year current month Vs Previous year same month data into pivot table

sunny_talwar — Thu, 13 Jul 2017 11:42:53 GMT

Or you can also look into

The As-Of Table

Re: How to show current year current month Vs Previous year same month data into pivot table

venkatbza — Thu, 13 Jul 2017 12:13:01 GMT

Hi Manish thanks for your reply.

Below(Total SUM({<Year, Month>}Sales)) * Avg(1)

I tried with your expression it is giving current year previous month total.

but my requirement is i need to show privies year same month total(July-2016)

if i select July 2017 in my list box, current sales should be sum of sales July-2017 and previous sales should be sum of July -2016

Re: How to show current year current month Vs Previous year same month data into pivot table

sunny_talwar — Thu, 13 Jul 2017 12:15:13 GMT

Try this

Below(Total SUM({<Year, Month>}Sales), 12) * Avg(1)

Re: How to show current year current month Vs Previous year same month data into pivot table

MK_QSL — Thu, 13 Jul 2017 12:15:44 GMT

What chart/object you are using?

what are the dimensions in that object?

Re: How to show current year current month Vs Previous year same month data into pivot table

venkatbza — Thu, 13 Jul 2017 12:29:52 GMT

i am using pivot table and my Dimensions is

Year and Month

Re: How to show current year current month Vs Previous year same month data into pivot table

venkatbza — Thu, 13 Jul 2017 12:33:03 GMT

Hi Sunny your expression is working fine when there is no year selections, if i select any year it is showing 0 values.

Re: How to show current year current month Vs Previous year same month data into pivot table

sunny_talwar — Thu, 13 Jul 2017 12:34:11 GMT

Really? We are ignoring selection in Year field.... can you share a sample where we can see the issue?

Re: How to show current year current month Vs Previous year same month data into pivot table

gsiva1220 — Sun, 03 Nov 2019 07:25:16 GMT

Hi, When I use this expression, Below(Total SUM({<[Fiscal Year], [Fiscal Month],[Fiscal Month Name]>}ExtendedNetPrice), 12) * Avg(1) I can see the correct output in pivot grid. See Pivot1.png But when I change the dimension from vertical to horizontal the expression is not working. See Pivot2.png