24 Replies Latest reply: Jan 11, 2018 1:07 PM by Sunny Talwar

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

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

May be you need this

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

• ###### Re: Incorrect stacked chart calculation in Sense

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

Did this also not work?

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

• ###### Re: Incorrect stacked chart calculation in Sense

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

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

• ###### Re: Incorrect stacked chart calculation in Sense

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

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

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

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

Check if the attached it what you want

• ###### Re: Incorrect stacked chart calculation in Sense

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

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

• ###### Re: Incorrect stacked chart calculation in Sense

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

• ###### Re: Incorrect stacked chart calculation in Sense

Attaching a Qlik Sense sample

• ###### Re: Incorrect stacked chart calculation in Sense

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

Damian -

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

• ###### Re: Incorrect stacked chart calculation in Sense

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

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

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:

Date(Floor([First Found Datetime])) as StartDate,

Date(If(Len(Trim([Last Fixed Datetime])) = 0, Today(), Floor([Last Fixed Datetime]))) as EndDate;

FROM [lib://New folder/all_vulns.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

MinMax:

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:

MonthName(TempDate) as MonthYear

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

IntervalMatch:

IntervalMatch (OrderDate)

EndDate

Resident Table;

• ###### Re: Incorrect stacked chart calculation in Sense

Can you share a screenshot of the error message you got?

• ###### Re: Incorrect stacked chart calculation in Sense

Sure

• ###### Re: Incorrect stacked chart calculation in Sense

It appears that you have script after what you have shared and that is where the error is taking place... I won't know until I see the unshared script

• ###### Re: Incorrect stacked chart calculation in Sense

Originally there was some script but it was all commented with // on each line.

I also previously thought that because there was a reference to End in that area that it might be the issue, so I removed all the commented script. Saved it and reloaded. Same problem.

The only info in the Data Editor is as shown above plus a setup piece at the top of the file as shown here:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Is there a cache somewhere  that the old editor script is in - that I can flush ?

• ###### Re: Incorrect stacked chart calculation in Sense

I have not used Qlik Sense too much to know about this, but I have not seen this kind of issue before. Not really sure what you can do... may be create a new version of the app to test it out?