Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
Contributor III
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

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
Contributor III
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
Contributor III
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?

Highlighted
Contributor II
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