topic Re: Sum of values from Jan to Dec for current year and last year in Pivot Table in QlikView

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

KuppuswamyS — Thu, 03 Dec 2020 10:43:13 GMT

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.

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

edwin — Thu, 03 Dec 2020 18:35:55 GMT

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

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

edwin — Thu, 03 Dec 2020 18:40:47 GMT

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)

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

KuppuswamyS — Fri, 04 Dec 2020 06:38:56 GMT

Hi Edwin,

Months are date fields and computed based on dates.

Thank you.

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

KuppuswamyS — Fri, 04 Dec 2020 06:44:51 GMT

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.

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

edwin — Fri, 04 Dec 2020 12:43:54 GMT

check out the attached if that will help

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

KuppuswamyS — Wed, 09 Dec 2020 05:45:15 GMT

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.

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

edwin — Wed, 09 Dec 2020 13:33:31 GMT

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

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

KuppuswamyS — Fri, 11 Dec 2020 09:41:35 GMT

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.

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

edwin — Tue, 15 Dec 2020 20:54:32 GMT

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

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

KuppuswamyS — Thu, 17 Dec 2020 04:17:53 GMT

Hi Edwin,

Requirement is to show last year average hours and YTD average hours and current year months in columns.

Please check the attached excel sheet. I'm using Pivot table chart to display months of current year, last year average hours and YTD average hours in columns .

Thank you.

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

edwin — Wed, 06 Jan 2021 19:56:29 GMT

normally, you cant have an expression outside of the pivots. if you add the two expressions

Last Year Average Actual Hours YTD Average Actual Hours

it will be pivoted - you will get these for Jan, Feb, Mar, etc..

what you do is aggregate them and add them as dimensions. this way the calculated value is not pivoted. i adjusted the QVW to show you the total for prior year. you can expand from that

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

edwin — Wed, 06 Jan 2021 19:56:55 GMT

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

KuppuswamyS — Tue, 12 Jan 2021 15:14:44 GMT

Hi Edwin,

It works, thank you.