Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mohbeen_deen
Contributor III
Contributor III

Creating a Bar Chart & Pivot Table with Variance Calculations

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?

1 Solution

Accepted Solutions
sunny_talwar

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!

View solution in original post

8 Replies
sunny_talwar

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?

mohbeen_deen
Contributor III
Contributor III
Author

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

sunny_talwar

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!

View solution in original post

mohbeen_deen
Contributor III
Contributor III
Author

Thank you Sunny! Worked fantastic!

This is how it came out :

Bar graph.PNG

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

sunny_talwar

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

1) Employee

2) Year

and then select the stacking

mohbeen_deen
Contributor III
Contributor III
Author

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?

Variance.PNG

sunny_talwar

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

mohbeen_deen
Contributor III
Contributor III
Author

Thank you! Appreciate it Sunny