topic Re: How to display trends with big changes in App Development

How to display trends with big changes

Mch201 — Sat, 16 Nov 2024 00:45:17 GMT

Hi, I have a set of data with customer, product purchased on weekly basis and quantity.

in the data, there are about 1000 customers. I only want to display the customers where there's a 30% drop or 30% jump in the quantity of orders for the most recent week. view the example below

customer	product	quantity
John	se22	6
John	se23	9
John	se22	3
John	se22	7
John	se22	6
John	se22	6
John	se22	87

As you can see john has been very consistent with his purchases except his latest purchase was huge compared to his previous purchase. this is the type of customer i want to view in form of a line chart displaying the trend.

Most customers are always consistent with heir order quantity, but in case there is a 30% drop or a jump, we want to display that on a line chart showing the trend

Re: How to display trends with big changes

PriyankaShivhare — Wed, 27 Jan 2021 02:19:53 GMT

please go thru the below link
http://dataonthe.rocks/outliers-in-qlik-sense/

hope this helps!

Thanks,
Priyanka

Re: How to display trends with big changes

PradeepK — Wed, 27 Jan 2021 05:00:57 GMT

whenever you want to show some exception .. try to find which property is best fit - Size, Shape ,Color

In Case of Line Chart data point Size and shape is constant for all.. So only option is yo use Color property.

Look into Color() and Colormix1() function in qlik - Docs

For Reference Only :

Color by expression

ColorMix1( (sum(quantity) - $(=Min(Aggr(sum(quantity), pdate, customer)))) / $(=Max(Aggr(sum(quantity), pdate, customer))) ,color(FieldIndex('customer',customer)), green() )

I hope this answers your question.

Re: How to display trends with big changes

PradeepK — Wed, 27 Jan 2021 05:10:21 GMT

Whenever you want to highlight exception data.. ask yourself what is the best property : Size, Shape , Color

In case of line chart Size and Shape is constant for all data points.. So you can use Color property

look into Color properties - doc

Code for Reference Only :

ColorMix1( (sum(quantity) - $(=Min(Aggr(sum(quantity), pdate, customer)))) / $(=Max(Aggr(sum(quantity), pdate, customer))) ,color(FieldIndex('customer',customer)), green() )

I hope this answers your question.

Re: How to display trends with big changes

Mch201 — Wed, 27 Jan 2021 13:42:35 GMT

This is helpful and good to know. But what if I only want to display top 5 customers with the biggest jump or drop?? how would I do something like that?

Re: How to display trends with big changes

Ksrinivasan — Thu, 28 Jan 2021 15:22:00 GMT

hi,

SOD:
Load
AutoNumber(RowNo()) as Order_Sequence,
customer,
product,
"quantity"
FROM [lib://REPORT EXTRACTION SSSS1.xlsx]
(ooxml, embedded labels, table is [custome sales drop]);

TTT:
Load *,
if((((Previous("quantity")/"quantity")* 100)-100) > 30 , 'High_Jump','') as High_PER,
if((((Previous("quantity")/"quantity")* 100)-100) < -30 , 'Low_Jump','') as Low_PER,
((Previous("quantity")/"quantity")* 100)-100 as Percentage
Resident SOD
Order by customer;

further drill down chart

in chart you can use limitation with 10 values with ascending or descending values.

i hope it will help to you.

ksrinivasan

Re: How to display trends with big changes

Mch201 — Thu, 28 Jan 2021 19:43:38 GMT

Thanks for the answer. where exactly would I put this script

Re: How to display trends with big changes

Ksrinivasan — Fri, 29 Jan 2021 02:59:36 GMT

hi,

in load script,

Load *,

((Previous("quantity")/"quantity")* 100)-100 as Percentage
Resident SOD
Order by customer;

in chart you can use

DIM: Customer

Measure: Max(Percentage)

Chart range in y-axis setting Max and Min range.

for >30%order min 30,

for <30%order min -30,

ksrinivasan