Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mattiasbrandt
Contributor II
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?


Thanks in advance!


/Mattias


1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

5 Replies
sunny_talwar

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

mattiasbrandt
Contributor II
Contributor II
Author

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.

sunny_talwar

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
Contributor II
Contributor II
Author

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

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