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: 
Anonymous
Not applicable

Incorrect stacked chart calculation in Sense

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.

vuln_split_q.jpg

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

24 Replies
sunny_talwar

Check if the attached it what you want

Capture.PNG

Anonymous
Not applicable
Author

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.

sunny_talwar

This is a QlikView app.. are you using Qlik Sense?

Anonymous
Not applicable
Author

Yes Qlik Sense. It's in the thread title but I didn't mention it since.

sunny_talwar

Attaching a Qlik Sense sample

Capture.PNG

Anonymous
Not applicable
Author

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.

sunny_talwar

Damian -

You are right about the first point. Looking at the second one right now

sunny_talwar

The values are getting accumulated over the dates... for example, Severity 3 accumulate over the dates....

Capture.PNG

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....

Anonymous
Not applicable
Author

OK I see how they accumulate. Thanks.

So to take the severity 3 rows only which have the following dates:

   

SeverityFirst Found DatetimeLast Found Datetime
32017-04-05T19:01:52Z2017-12-05T01:20:59Z
32017-04-11T11:14:12Z2017-10-24T18:50:15Z

I am looking for a chart that looks similar to this mockup below:

mockup.jpg

The other severities would stack above the 3's to give the full picture.

For a fuller example see the image below:

2ndexample.jpg

I hope that helps.

Anonymous
Not applicable
Author

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;