# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

## Create a histogram with a calculated value

Hi guys,

I'm new to Qlikview and I am trying to create an histogram för the number of occurences of a calculated value.

To clarify my problem, I have the following columns:

ActivityNumberMeeting_bookedMeeting_CancelledDays_till_cancelled
1251062017-01-152017-05-15120
1478412017-02-012017-02-054
............

I have calculated the diffrence between the two dates in the new column Days_till_cancelled by using:

max({\$Activity={'Booked'}> Date}-max({\$Activity={'Cancelled'}> Date} = Difference in days

Now I would like to create an histogram based on this new column to see if there is any signs that meetings that are booked far ahead gets more frequently cancelled than meetings that are booked sooner.

I added the calculation as a variable and tried creating the table with Round(Value,Binwidth), as described by hic in his blogpost Recipe for a Histogram. However, this does not work when the value is a calculated variable.

Any ideas how I can create this?

/Mattias

Tags (4)
1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Create a histogram with a calculated value

Are you looking for something like this?

Dimension

=Aggr(If(MeetingCancelled - Meeting_Booked < 100, Dual('<100 days', 1),

If(MeetingCancelled - Meeting_Booked < 200, Dual('>=100<200 days', 2),

If(MeetingCancelled - Meeting_Booked < 300, Dual('>=200<300 days', 3),

If(MeetingCancelled - Meeting_Booked < 400, Dual('>=300<400 days', 4),

If(MeetingCancelled - Meeting_Booked < 500, Dual('>=400<500 days', 5)))))), ActivityNumber)

Expression

=Count(DISTINCT ActivityNumber)

5 Replies
Highlighted
MVP

## Re: Create a histogram with a calculated value

Would you be able to share a sample where you are trying to do this? How exactly have you done this?

Highlighted
New Contributor II

## Re: Create a histogram with a calculated value

Hi stalwar1,

Unfurtunaly not, there is more data that calculates the dates for each activity.

But for the sake of of, this is a sample table:

 ActivityNumber MeetingCancelled Meeting_Booked 880678 2017-12-31 2016-10-21 245361 2017-11-21 2017-08-10 264368 2017-10-23 2017-02-03 537748 2017-10-17 2017-08-22 428233 2017-10-16 2017-08-22 160581 2017-10-12 2017-08-25 393221 2017-10-11 2017-08-24 843339 2017-10-09 2017-04-04 234093 2017-10-09 2016-11-16 312729 2017-10-09 2017-04-20 757062 2017-10-06 2017-08-25 350201 2017-10-05 2017-04-05

To calculate the days between the dates, I used: Meeting_Booked-MeetingCancelled.

So for the first row, I get 436 days, secon 327 days etc. So now the table is like this:

 ActivityNumber MeetingCancelled Meeting_Booked Days_till_Cancelled 880678 2017-12-31 2016-10-21 -436 245361 2017-11-21 2017-08-10 -103 264368 2017-10-23 2017-02-03 -262 537748 2017-10-17 2017-08-22 -56 428233 2017-10-16 2017-08-22 -55 160581 2017-10-12 2017-08-25 -48 393221 2017-10-11 2017-08-24 -48 843339 2017-10-09 2017-04-04 -188 234093 2017-10-09 2016-11-16 -327 312729 2017-10-09 2017-04-20 -172 757062 2017-10-06 2017-08-25 -42 350201 2017-10-05 2017-04-05 -183

So now I want to make a histogram to see the distrubution of days a meeting get cancelled, and I have no clue how to do this with a calculated value.

Highlighted
MVP

## Re: Create a histogram with a calculated value

Are you looking for something like this?

Dimension

=Aggr(If(MeetingCancelled - Meeting_Booked < 100, Dual('<100 days', 1),

If(MeetingCancelled - Meeting_Booked < 200, Dual('>=100<200 days', 2),

If(MeetingCancelled - Meeting_Booked < 300, Dual('>=200<300 days', 3),

If(MeetingCancelled - Meeting_Booked < 400, Dual('>=300<400 days', 4),

If(MeetingCancelled - Meeting_Booked < 500, Dual('>=400<500 days', 5)))))), ActivityNumber)

Expression

=Count(DISTINCT ActivityNumber)

Highlighted
New Contributor II

## Re: Create a histogram with a calculated value

YES!

Exactly what I needed, thank you!

A follow up question, I want to ommit all where the Days_till_cancelled is null, since the meeting is not cancelled.

The majority of meetings does not get cancelled, so it would be nice the not have that in my diagram.

Example of the table again but with blank cells in MeetingCancelled

 ActivityNumber MeetingCancelled Meeting_Booked 880678 2017-12-31 2016-10-21 245361 2017-11-21 2017-08-10 264368 2017-10-23 2017-02-03 537748 2017-10-17 2017-08-22 428233 2017-08-22 160581 2017-10-12 2017-08-25 393221 2017-10-11 2017-08-24 843339 2017-10-09 2017-04-04 234093 2016-11-16 312729 2017-10-09 2017-04-20 757062 2017-08-25 350201 2017-10-05 2017-04-05
Highlighted
MVP