Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

mattiasbrandt
New Contributor

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?


Thanks in advance!


/Mattias


1 Solution

Accepted Solutions

Re: Create a histogram with a calculated value

Are you looking for something like this?

Capture.PNG

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

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?

mattiasbrandt
New Contributor

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:

 

ActivityNumberMeetingCancelledMeeting_Booked
8806782017-12-312016-10-21
2453612017-11-212017-08-10
2643682017-10-232017-02-03
5377482017-10-172017-08-22
4282332017-10-162017-08-22
1605812017-10-122017-08-25
3932212017-10-112017-08-24
8433392017-10-092017-04-04
2340932017-10-092016-11-16
3127292017-10-092017-04-20
7570622017-10-062017-08-25
3502012017-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:

  

ActivityNumberMeetingCancelledMeeting_BookedDays_till_Cancelled
8806782017-12-312016-10-21-436
2453612017-11-212017-08-10-103
2643682017-10-232017-02-03-262
5377482017-10-172017-08-22-56
4282332017-10-162017-08-22-55
1605812017-10-122017-08-25-48
3932212017-10-112017-08-24-48
8433392017-10-092017-04-04-188
2340932017-10-092016-11-16-327
3127292017-10-092017-04-20-172
7570622017-10-062017-08-25-42
3502012017-10-052017-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.

Re: Create a histogram with a calculated value

Are you looking for something like this?

Capture.PNG

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
New Contributor

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

ActivityNumberMeetingCancelledMeeting_Booked
8806782017-12-312016-10-21
2453612017-11-212017-08-10
2643682017-10-232017-02-03
5377482017-10-172017-08-22
4282332017-08-22
1605812017-10-122017-08-25
3932212017-10-112017-08-24
8433392017-10-092017-04-04
2340932016-11-16
3127292017-10-092017-04-20
7570622017-08-25
3502012017-10-05

2017-04-05

Re: Create a histogram with a calculated value

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

Community Browser