topic Re: Incorrect stacked chart calculation in Sense in QlikView

Incorrect stacked chart calculation in Sense

Anonymous — Fri, 15 Dec 2017 09:36:08 GMT

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

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Fri, 15 Dec 2017 12:40:09 GMT

May be you need this

Aggr(RangeSum(Above(Count(Severity), 0, RowNo())), Severity, Month)

Re: Incorrect stacked chart calculation in Sense

Anonymous — Fri, 15 Dec 2017 13:52:53 GMT

Hi Sunny,

I'm afraid that removes all plots. I think it's because I'm not tracking by month, but from a datetime string included with every value. Each line in the source has a field called (First Found Datetime) in the format "2017-09-13T08:56:29Z". I use this as a dimension, along with Severity (integer 3, 4 or 5). I've played with the idea of aggregation but not come up with the fix yet.

Damian

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Fri, 15 Dec 2017 16:45:50 GMT

Did this also not work?

Aggr(RangeSum(Above(Count(Severity), 0, RowNo())), Severity, datetime)

Re: Incorrect stacked chart calculation in Sense

Anonymous — Fri, 15 Dec 2017 18:55:35 GMT

Yes with a tweak. I had to enclose the last field in quotes.

Aggr(RangeSum(Above(Count(Severity), 0, RowNo())), Severity, "First Found Datetime")

Thanks very much Sunny

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Fri, 15 Dec 2017 19:01:29 GMT

Sure, and that is because there are spaces, but the logic still stays the same that you needed to use Aggr() function first by Severity and then your date and time field...

Please close the thread if you got what you wanted by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Incorrect stacked chart calculation in Sense

Anonymous — Fri, 15 Dec 2017 20:59:36 GMT

Will do.

I hit another problem though now that this one is cleared (couldn't see it before).

All data lines are of status New, Open, Fixed or Re-opened. I've been treating all as adding to the total, which is incorrect. The Fixed status ones need removing from the relevant total of either 3, 4 or 5 severity depending on which that Fixed issue was.

The data is a historic snapshot of what happened.

For example:

It shows 3x Severity 5 vulnerabilities with "First Found Datetime" of 01JAN17 and no "Last Found Datetime" (there is no data in the CSV field for "Last Fixed Datetime"). So those vulnerabilities are all still active (no Fixed date).

It shows another Severity 5 vulnerability "First Found Datetime" of 01JAN17 but that line has a "Last Found Datetime" of 12JAN17. So this one is now Fixed but should show only from 01JAN17 to 12JAN17. After that is no longer an issue.

Is my description understandable ?

I appreciate your help with this Sunny.

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Sat, 16 Dec 2017 11:48:15 GMT

Unfortunately, I don't complete follow... would you be able to share few rows of raw data to explain what exactly are you looking to get from it?

Re: Incorrect stacked chart calculation in Sense

Anonymous — Tue, 19 Dec 2017 07:49:06 GMT

Yes - lines in CSV format below. I can't see how to attach the whole file.

Host ID,IP Address,Tracking Method,Operating System,DNS Name,Netbios Name,QG HostID,Last Scan Datetime,OS CPE,Last VM Scanned Date,Last VM Scanned Duration,Last VM Auth Scanned Date,Last VM Auth Scanned Duration,Last PC Scanned Date,QID,Type,Port,Protocol,FQDN,SSL,Instance,Status,Severity,First Found Datetime,Last Found Datetime,Last Test Datetime,Last Update Datetime,Last Fixed Datetime,Results,Ignored,Disabled,Times Found,Service,Last Processed Datetime

107355502,192.168.100.71,AGENT,Microsoft Windows 10 Home 10.0.15063 N/A Build 15063,dell-13,DELL-13,f223930d-80cc-4cd8-8849-52341d5af8d5,2017-12-05T01:21:34Z,,2017-12-05T01:20:59Z,,2017-12-05T01:20:59Z,,2017-12-04T11:06:11Z,90042,Potential,,,,0,,Active,3,2017-04-05T19:01:52Z,2017-12-05T01:20:59Z,2017-12-05T01:20:59Z,2017-12-05T01:21:34Z,,,0,0,69,,2017-12-05T01:21:34Z

107355502,192.168.100.71,AGENT,Microsoft Windows 10 Home 10.0.15063 N/A Build 15063,dell-13,DELL-13,f223930d-80cc-4cd8-8849-52341d5af8d5,2017-12-05T01:21:34Z,,2017-12-05T01:20:59Z,,2017-12-05T01:20:59Z,,2017-12-04T11:06:11Z,90126,Potential,,,,0,,Fixed,3,2017-04-11T11:14:12Z,2017-10-24T18:50:15Z,2017-12-05T01:20:59Z,2017-12-05T01:21:34Z,2017-12-04T11:06:11Z,,0,0,49,,2017-12-05T01:21:34Z

107355502,192.168.100.71,AGENT,Microsoft Windows 10 Home 10.0.15063 N/A Build 15063,dell-13,DELL-13,f223930d-80cc-4cd8-8849-52341d5af8d5,2017-12-05T01:21:34Z,,2017-12-05T01:20:59Z,,2017-12-05T01:20:59Z,,2017-12-04T11:06:11Z,91340,Confirmed,,,,0,,Fixed,4,2017-04-05T19:01:52Z,2017-04-05T20:30:21Z,2017-12-05T01:20:59Z,2017-12-05T01:21:34Z,2017-04-07T19:33:15Z,,0,0,2,,2017-12-05T01:21:34Z

107355502,192.168.100.71,AGENT,Microsoft Windows 10 Home 10.0.15063 N/A Build 15063,dell-13,DELL-13,f223930d-80cc-4cd8-8849-52341d5af8d5,2017-12-05T01:21:34Z,,2017-12-05T01:20:59Z,,2017-12-05T01:20:59Z,,2017-12-04T11:06:11Z,91353,Confirmed,,,,0,,Fixed,5,2017-04-12T19:18:34Z,2017-08-03T18:02:57Z,2017-12-05T01:20:59Z,2017-12-05T01:21:34Z,2017-08-04T13:36:26Z,,0,0,3,,2017-12-05T01:21:34Z

107355502,192.168.100.71,AGENT,Microsoft Windows 10 Home 10.0.15063 N/A Build 15063,dell-13,DELL-13,f223930d-80cc-4cd8-8849-52341d5af8d5,2017-12-05T01:21:34Z,,2017-12-05T01:20:59Z,,2017-12-05T01:20:59Z,,2017-12-04T11:06:11Z,91409,Confirmed,,,,0,,Active,5,2017-09-18T14:57:23Z,2017-12-05T01:20:59Z,2017-12-05T01:20:59Z,2017-12-05T01:21:34Z,,,0,0,5,,2017-12-05T01:21:34Z

107355502,192.168.100.71,AGENT,Microsoft Windows 10 Home 10.0.15063 N/A Build 15063,dell-13,DELL-13,f223930d-80cc-4cd8-8849-52341d5af8d5,2017-12-05T01:21:34Z,,2017-12-05T01:20:59Z,,2017-12-05T01:20:59Z,,2017-12-04T11:06:11Z,91410,Confirmed,,,,0,,Fixed,5,2017-09-18T14:57:23Z,2017-09-18T14:57:23Z,2017-12-05T01:20:59Z,2017-12-05T01:21:34Z,2017-10-11T15:08:45Z,,0,0,1,,2017-12-05T01:21:34Z

107355502,192.168.100.71,AGENT,Microsoft Windows 10 Home 10.0.15063 N/A Build 15063,dell-13,DELL-13,f223930d-80cc-4cd8-8849-52341d5af8d5,2017-12-05T01:21:34Z,,2017-12-05T01:20:59Z,,2017-12-05T01:20:59Z,,2017-12-04T11:06:11Z,91413,Confirmed,,,,0,,New,5,2017-12-05T01:20:59Z,2017-12-05T01:20:59Z,2017-12-05T01:20:59Z,2017-12-05T01:21:34Z,,,0,0,1,,2017-12-05T01:21:34Z

All lines that are in this file need to show in the line chart but the ones which have status fixed need to stop showing in the chart from that fixed date/time.

For example, new issues are found over time and the chart shows a steady increase in volume of issues. If then there is a patch blitz and many issues are fixed on the same day those issues shouldn't disappear from the whole chart. instead they should show until the date they were patched.

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Wed, 20 Dec 2017 02:00:25 GMT

Based on what I am understand, it almost seems like you need an IntervalMatch. I will try to get a sample across....

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Wed, 20 Dec 2017 02:07:25 GMT

Check if the attached it what you want

Re: Incorrect stacked chart calculation in Sense

Anonymous — Fri, 05 Jan 2018 12:22:50 GMT

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.

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Fri, 05 Jan 2018 12:40:20 GMT

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

Re: Incorrect stacked chart calculation in Sense

Anonymous — Fri, 05 Jan 2018 13:19:31 GMT

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

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Fri, 05 Jan 2018 13:23:17 GMT

Attaching a Qlik Sense sample

Re: Incorrect stacked chart calculation in Sense

Anonymous — Fri, 05 Jan 2018 18:23:47 GMT

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.

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Fri, 05 Jan 2018 18:34:27 GMT

Damian -

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

Re: Incorrect stacked chart calculation in Sense

sunny_talwar — Fri, 05 Jan 2018 20:31:40 GMT

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

Re: Incorrect stacked chart calculation in Sense

Anonymous — Fri, 05 Jan 2018 21:07:14 GMT

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.

Re: Incorrect stacked chart calculation in Sense

Anonymous — Thu, 11 Jan 2018 14:30:25 GMT

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;