Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to show the breakdown of a count of vulnerabilities by severity. Severities are either 3,4 or 5. The chart is done by time on the X axis against count of vulnerabilities on the Y.
My issue is that although the total number of vulnerabilities is correct (1,346) the split in the chart is not. See the graphic below. The chart shows them roughly equally split but the totals to the side are the real values and show a different split. Blues are Severity 3, Yellow 4 and Red 5.
The measure I'm using has the following calculation:
RANGESUM(ABOVE(TOTAL Count(Severity),0, ROWNO(TOTAL)))
Can anyone suggest how to correct the split in this calculation please ?
Thanks
Check if the attached it what you want
Hi Sunny,
I'm afraid I can't open that in Sense.
I dropped it into the apps dir and pointed Sense to it but it contains no sheet so I can't see the IntervalMatch code.
This is a QlikView app.. are you using Qlik Sense?
Yes Qlik Sense. It's in the thread title but I didn't mention it since.
Attaching a Qlik Sense sample
Thanks Sunny. That's much closer to what I need . I can stack that chart and it looks pretty close now. Just a couple of newbie questions:
1. In the data load editor you added a script for the 3 tables. The main data table is created from a load statement. Can I point that load statement at the incoming data file instead ? That is, Select a file to load and have this script parse it ?
2. The resulting chart has a total count of around 560 on the Y axis but there are only 7 data lines being read so it shouldn't be more than 7 on the Y axis. I don't see where this is being expanded to 560.
Damian -
You are right about the first point. Looking at the second one right now
The values are getting accumulated over the dates... for example, Severity 3 accumulate over the dates....
I am not sure what else are you expecting to see? If you can, I would ask you to share the output you expect to see for the given data....
OK I see how they accumulate. Thanks.
So to take the severity 3 rows only which have the following dates:
Severity | First Found Datetime | Last Found Datetime |
3 | 2017-04-05T19:01:52Z | 2017-12-05T01:20:59Z |
3 | 2017-04-11T11:14:12Z | 2017-10-24T18:50:15Z |
I am looking for a chart that looks similar to this mockup below:
The other severities would stack above the 3's to give the full picture.
For a fuller example see the image below:
I hope that helps.
Hi Sunny,
You seemed to answer a very similar question to mine here
Although that answer needed bars by month, the logic looks the same. Each data point shows active issues/projects on that date. I need it by day (not month) but even by month would be a sign that it's on the right path.
I tried to modify it for my own use but have a "Field 'End' not found error".
Table:
LOAD *,
Date(Floor([First Found Datetime])) as StartDate,
Date(If(Len(Trim([Last Fixed Datetime])) = 0, Today(), Floor([Last Fixed Datetime]))) as EndDate;
LOAD *
FROM [lib://New folder/all_vulns.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
MinMax:
LOAD Min(StartDate) as MinDate,
Max(EndDate) as MaxDate
Resident Table;
LET varMinDate = Peek('MinDate');
LET varMaxDate = Peek('MaxDate');
DROP Table MinMax;
TempCalendar:
LOAD Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1
While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load TempDate as OrderDate,
MonthName(TempDate) as MonthYear
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
IntervalMatch:
IntervalMatch (OrderDate)
LOAD StartDate,
EndDate
Resident Table;