topic Creating a Bar Chart & Pivot Table with Variance Calculations in QlikView

Creating a Bar Chart & Pivot Table with Variance Calculations

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I have two questions:

My aim is to create a a bar chart that has 2 year dimensions (2017, 2018) that should accumulate per employee for the number of leave days taken. For instance, if Employee A took 4 days of leave in 2017 and then 5 in 2018 - The bar graph should show both 2017 and 2018 as one bar but accumulate. It would show as 9 days but 2017 would be 4 days and 2018 would be indicated as 5.

How best can I achieve this?

I would then like to create a Pivot table where each employees name would be listed and then a view of each month would be across the pivot table. Each column would have a month comparison from the previous year. eg April 2017 for Employee A would have 5 days and then April 2018 would have 3 days, there should be a variance column that would reflect a difference of 2 days.

How do I create this along with the Syntax?

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

sunny_talwar — Tue, 08 May 2018 11:48:16 GMT

1) May be try this

Aggr(RangeSum(Above(Count(Leave), 0, RowNo())), Employee, Year)

2) What syntax? Do you have a sample you can share to show what you have... so that we can help better

How do I create this along with the Syntax?

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

Anonymous — Tue, 08 May 2018 11:59:50 GMT

Hi Sunny- Thanks for your speedy response

I'm relatively new to Qlik, please could you explain step by step how to carry out this for question no.1

1) what columns would be the dimensions be as well as the expressions. Where would the expression you provided be typed in?

2) I will provide the sample data for question 2

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

sunny_talwar — Tue, 08 May 2018 12:04:58 GMT

Hi Mohbeen, welcome to the world of Qlik... I am not sure what you mean when you say that you want to know the steps. There are no steps... All you need is a bar chart with two dimensions

1) Year

2) Employee

and then use the expression that I gave above and it should most likely work... if for some reason it doesn't work and you are using QV12 or above or Qlik Sense, you can try this expression

Aggr(RangeSum(Above(Count(Leave), 0, RowNo())), Employee, (Year, (NUMERIC)))

The above syntax is a new option that came out with QV12. You can read about it more here

The sortable Aggr function is finally here!

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

Anonymous — Tue, 08 May 2018 12:14:16 GMT

Thank you Sunny! Worked fantastic!

This is how it came out :

I know how to change it to a stacked bar graph, however how do I stack both 2017 and 2018 per employee?

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

sunny_talwar — Tue, 08 May 2018 12:17:15 GMT

I guess in that case, you might need to switch your dimensions

1) Employee

2) Year

and then select the stacking

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

Anonymous — Tue, 08 May 2018 12:35:20 GMT

With regards to Question 2, here's a sample of what I'm trying to achieve but with a pivot table or table view:

The dimensions Would be Employee and Leave, what would the expressions look like?

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

sunny_talwar — Tue, 08 May 2018 13:02:44 GMT

I would use set analysis to do this

Expression1

Sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YY'))"}>}Measure)

Expression2

Sum({<MonthYear = {"$(=Date(AddYears(Max(MonthYear), -1), 'MMM-YY'))"}>}Measure)

Expression3

Column(1) - Column(2)

Where MonthYear is created in the script like this

Date(MonthStart(DateField), 'MMM-YY') as MonthYear

Re: Creating a Bar Chart & Pivot Table with Variance Calculations

Anonymous — Tue, 08 May 2018 13:14:40 GMT

Thank you! Appreciate it Sunny