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

[KuppuswamyS]

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.

[edwin]

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]

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]

Hi Edwin,

           Months are date fields and computed based on dates. 

          Thank you. 

[KuppuswamyS]

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. 

[edwin]

check out the attached if that will help

[KuppuswamyS]

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]

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]

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. 

[edwin]

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

and then add a %:

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