Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

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

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.

YearMonthCurrent year  salesPrevious sales
2017July50006000
2016July60004500
2015July45007600
2014July76003400
2013July34002000
9 Replies
Highlighted
MVP
MVP

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

Dimension

Year

Month

Expression

SUM(Sales)

&

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

Highlighted

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

Or you can also look into

The As-Of Table

Highlighted
New Contributor III

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

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

Highlighted

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

Try this

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

Highlighted
MVP
MVP

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

What chart/object you are using?

what are the dimensions in that object?

Highlighted
New Contributor III

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

i am using pivot table and my Dimensions is

Year and Month

Highlighted
New Contributor III

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

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

Highlighted

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

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

New Contributor II

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

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