Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for
Did you mean:
Contributor II

## Sum of values from Jan to Dec for current year and last year in Pivot Table

Hello All,

Can you please help how to sum Actual hours for current year(Jan to Dec) and previous year(Jan to Dec) and display it after plant column in the Pivot Table shown below?  Thank you.

1 Solution

Accepted Solutions
Master II
13 Replies
Master II

Assuming your Months are just text fields and not date fields, you should just be able to add YEAR as the second dimension in your pivot table.  of course YEAR should be calculated in your calendar.  it will then look like the attached

Master II

of course in your expression you should have a set analysis that limits it to current year and last year.  the way i would do that is create a variable for current year and prior year

vCurrentYear=max(Year) and vPriorYear=vCurrentYear-1

=sum( {<Year={\$(vCurrentYear),\$(vPriorYear)}>}measure)

it could also be this way:

=sum( {<Year={\$(=max(Year)),\$(=max(Year)-1)}>}measure)

Contributor II
Author

Hi Edwin,

Months are date fields and computed based on dates.

Thank you.

Contributor II
Author

Hi Edwin,

Your solution works but since months are calculated based on dates and it is a pivot table, I couldn't get value for previous year but previous year's months(Jan to Dec).  May I know how to get previous year value in pivot table? Thank you.

Master II

check out the attached if that will help

Contributor II
Author

Hi Edwin,

I tried using formula in your qvw and able to get only for months but not year as this is pivot table.

Is there anyway to compute values in load statement and display it as dimension?

Thank you.

Master II

it was intended to show you that it is possible to group both years under the same month.  take a look at this one

Contributor II
Author

Hi Edwin,

Sorry, Month field(computed based on date fields) is moved from Vertical axis to Horizontal axis in pivot table. So QlikView computes values on monthly basis but not year.

I need to have value for last year and current year in vertical axis followed by Months for current year in horizontal axis.

Thank you.

Master II

not sure what the issue is.   month and both years are at the top