find ORG names which has Skews in line chart qliks... - Qlik Community

SK28 · ‎2023-11-15

Hi,

I need find the ORG name which has sudden upwards Skews in the line chart, based on the count of the tickets, there are more than 400+ ORGs and the date range is Consistent across for all ORGs, I need to find the ORGs which has sudden SPIKE in the tickets

Here is the Data Sample data:

ORG	date	Count of tickts
TCS	10/11/2023	10
TCS	10/12/2023	12
TCS	10/13/2023	30
TCS	10/14/2023	12
TCS	10/15/2023	10
CTS	10/11/2023	10
CTS	10/12/2023	25
CTS	10/13/2023	9
CTS	10/14/2023	12
Wipro	10/14/2023	8
Wipro	10/15/2023	7
Infy	10/11/2023	6
Infy	10/12/2023	5

here is the line chart for above table

required output will be --> TCS on 10/13/2023 and CTS on 10/12/2023
or
TCS,CTS ORG names will also works

2nd Requirement:

Find the Name of the ORG, which has increase in the Count of Tickets

ORG	Name	Count of tickts
TCS	10/11/2023	10
TCS	10/12/2023	12
TCS	10/13/2023	13
TCS	10/14/2023	14
TCS	10/15/2023	17
TCS	10/16/2023	18
TCS	10/17/2023	19
TCS	10/18/2023	20
CTS	10/11/2023	11
CTS	10/12/2023	11
CTS	10/13/2023	12
CTS	10/14/2023	10
CTS	10/15/2023	8
CTS	10/16/2023	7

Required Output will be - TCS

Appreciate your help!

WaltShpuntoff · ‎2023-11-16

I understand you are trying to identify the "spiked" Org here. The question I have is where you are looking to see that information.

Keep in mind that you can select the date with the spike which will narrow all activity down to that date. You can define alternate dimensions and measures in the chart, so if you define an alternate dimension for the Org you could then flip the display and I believe that will show you what you are looking for.

- ws

Or · ‎2023-11-16

This isn't a particularly clear requirement... "Spike" and "increase" both need to be quantified.

Spikes should be fairly easy to find once defined properly - either by comparing the count of tickets to the Previous() value, or by comparing the count to a count from the day before using set analysis. You would then check if that value is higher than whatever threshold you've defined,e.g. if the ratio is greater than 200%.

A gradual increase is a bit trickier, but again this depends on the exact definition. If you're just looking for cases where the total for today is more than, say, 50% of the total from last week, regardless of behavior in the middle section, that would be the same as the solution for a spike. If it needs to be a full rising line, where each date is higher than the one before, I can't think of an easy way to do it in the front end without manually comparing the required number of days. Perhaps someone else will have a way to do that.

SK28 · ‎2023-11-16

@SK28 wrote:

Hi,

I need find the ORG name which has sudden upwards Skews in the line chart, based on the count of the tickets, there are more than 400+ ORGs and the date range is Consistent across for all ORGs, I need to find the ORGs which has sudden SPIKE in the tickets

Here is the Data Sample data:

ORG date Count of tickts

TCS 10/11/2023 10

TCS 10/12/2023 12

TCS 10/13/2023 30

TCS 10/14/2023 12

TCS 10/15/2023 10

CTS 10/11/2023 10

CTS 10/12/2023 25

CTS 10/13/2023 9

CTS 10/14/2023 12

Wipro 10/14/2023 8

Wipro 10/15/2023 7

Infy 10/11/2023 6

Infy 10/12/2023 5

here is the line chart for above table

required output will be --> TCS on 10/13/2023 and CTS on 10/12/2023
or
TCS,CTS ORG names will also works

2nd Requirement:

Find the Name of the ORG, which has increase in the Count of Tickets

ORG Name Count of tickts

TCS 10/11/2023 10

TCS 10/12/2023 12

TCS 10/13/2023 13

TCS 10/14/2023 14

TCS 10/15/2023 17

TCS 10/16/2023 18

TCS 10/17/2023 19

TCS 10/18/2023 20

CTS 10/11/2023 11

CTS 10/12/2023 11

CTS 10/13/2023 12

CTS 10/14/2023 10

CTS 10/15/2023 8

CTS 10/16/2023
7

Required Output will be - TCS

Appreciate your help!

Thanks for the response @Or & @WaltShpuntoff

The whole intention here is We want to find these two observations and we are trying to automate them , trigger them to LEADERSHIP team so that...using NPRINTING

then users will start using the dashboard more intensively to take corrective decisions

Doing this help

1)Help users identify these two observations and act on them

2) Qliksense product usuage will Improve in our Organization.

for request 1)

and we don't have any thesholds limits set here, if there is any sudden spike let's consider say >=75%

we need to find the ORG name...! which has sudden spike or skew in the data

and for point 2) if the count of tickets are continuously growing by Date... we need to find those observations as well.

we tried it using SKEW & Stdev But I got struck in midway...!! using above data even counts which are falling are eventually coming down continuously are also showing up.

If you can please help me with the logic, That would be a great help...!! like Previous() it will be helpful

@Or Spikes should be fairly easy to find once defined properly -

either by comparing the count of tickets to the Previous() value, or by comparing the count to a count from the day before using set analysis.

Using Above same Sample data please...!

WaltShpuntoff · ‎2023-11-16

I do not know your environment, but if you have the Insight Advisor active, you can ask it for Anomaly detection.

Or · ‎2023-11-19

Checking for a daily increase of 75% is pretty easy. You just pull today's value and yesterday's value and compare them, so your table would, with null/zero hidden, be a dimension of org, and a measure along the lines of:

if(Count({< Date = {"=Today()"} Tickets) / Count({< Date = {"=Today()-1"} Tickets) > 1.75, 'Spike')

Aasir · ‎2023-11-19

Use below two expressions

if(Count([Count of tickets]) > 0,
((Count([Count of tickets]) - Previous([Count of tickets])) / Previous([Count of tickets])) * 100,
null()
)

sum(if(Count([Count of tickets]) > 0,
((Count([Count of tickets]) - Previous([Count of tickets])) / Previous([Count of tickets])) * 100,
null()
))

find ORG names which has Skews in line chart qliksense

Chart

Data Load Editor

Developers

dimension

expression

filter

General Question

Script

Set Analysis

Variables

Visualization