Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
KuppuswamyS
Contributor II
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.

KuppuswamyS_1-1606992047631.png

 

 

1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

13 Replies
edwin
Specialist III
Specialist III

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

edwin
Specialist III
Specialist III

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

then in your expression   

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

it could also be this way:

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

KuppuswamyS
Contributor II
Contributor II
Author

Hi Edwin,

           Months are date fields and computed based on dates. 

          Thank you. 

KuppuswamyS
Contributor II
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. 

KuppuswamyS_1-1607064151041.png

 

edwin
Specialist III
Specialist III

check out the attached if that will help

 

KuppuswamyS
Contributor II
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. 

edwin
Specialist III
Specialist III

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

KuppuswamyS
Contributor II
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. 

KuppuswamyS_1-1607678839429.png

 

edwin
Specialist III
Specialist III

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

edwin_1-1608065292374.png

 

and then add a %:

edwin_2-1608065414119.png

 

maybe you need to explain the requirements.  its not easily discernible from your picture what numbers you need it sounds like you wanted months somewhere else but your picture shows it to be in the column not rows.

not sure what you mean by vertical - did you mean in the columns?  and when you say year in the horizontal did you mean the rows as in dimensions?

you will then have prior and current year