Create a histogram with a calculated value

[mattiasbrandt]

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:

ActivityNumber	Meeting_booked	Meeting_Cancelled	Days_till_cancelled
125106	2017-01-15	2017-05-15	120
147841	2017-02-01	2017-02-05	4
...	...	...	...

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?

Thanks in advance!

/Mattias

[sunny_talwar]

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

[mattiasbrandt]

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.

[sunny_talwar · Accepted solution]

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)

[mattiasbrandt]

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

[sunny_talwar]

Change your expression to this

=Count(DISTINCT {<ActivityNumber = {"=Len(Trim(MeetingCancelled)) > 0"}>} ActivityNumber)

or just check on Suppress When Value Is Null on the Dimension tab of chart properties