topic Re: highlet highest and lowest values based on selection in QlikView

highlet highest and lowest values based on selection

Wed, 25 Nov 2020 16:16:04 GMT

I have scenario

I have data like below

Date Location Sales Growth

2016-01-01 Delhi 10000 -10000

2016-02-01 Delhi 15000 50%

2016-03-01 Delhi 20000 33%

2016-04-01 Delhi 10000 -50%

2016-05-01 Delhi 30000 200%

2016-06-01 Delhi 45000 50%

2016-07-01 Delhi 60000 33%

2016-08-01 Delhi 50000 -20%

2016-09-01 Delhi 100000 100%

Growth is multibox

If i select filter as 5 Means last 5 months

i want to highlet highest value and lowest value

Growth =5 then

Date Location Sales Growth

2016-04-01 Delhi 10000 -50%

2016-05-01 Delhi 30000 200%

2016-06-01 Delhi 45000 50%

2016-07-01 Delhi 60000 33%

2016-08-01 Delhi 50000 -20%

2016-09-01 Delhi 100000 100%

if Growth =3 then

2016-07-01 Delhi 60000 33%

2016-08-01 Delhi 50000 -20%

2016-09-01 Delhi 100000 100%

Please do the needful

Thiru

Re: highlet highest and lowest values based on selection

sunny_talwar — Fri, 30 Sep 2016 12:44:11 GMT

Growth is percentage, How can you select 5 or 3?

Re: highlet highest and lowest values based on selection

Fri, 30 Sep 2016 12:51:38 GMT

Hi Sunny,

That is months from Now

for example 5 means from now to last 5 months

3 means

from now last 3 months

Thiru

Re: highlet highest and lowest values based on selection

sunny_talwar — Fri, 30 Sep 2016 12:52:41 GMT

So is there another field that you have? or is that you wish to create in the script?

Re: highlet highest and lowest values based on selection

Fri, 30 Sep 2016 12:53:42 GMT

Date Location Sales Growth Month_No

2016-01-01 Delhi 10000 -10000 9

2016-02-01 Delhi 15000 50% 8

2016-03-01 Delhi 20000 33% 7

2016-04-01 Delhi 10000 -50% 6

2016-05-01 Delhi 30000 200% 5

2016-06-01 Delhi 45000 50% 4

2016-07-01 Delhi 60000 33% 3

2016-08-01 Delhi 50000 -20% 2

2016-09-01 Delhi 100000 100% 1

some thing like this

Thiru

Re: highlet highest and lowest values based on selection

Fri, 30 Sep 2016 12:56:23 GMT

Hi Sunny

Month_No is Growth filter here

Thiru

Re: highlet highest and lowest values based on selection

antoniotiman — Fri, 30 Sep 2016 13:05:15 GMT

Hi,

see Attachment.

Regards,

Antonio

Re: highlet highest and lowest values based on selection

sunny_talwar — Fri, 30 Sep 2016 13:07:39 GMT

Check the attacted

Re: highlet highest and lowest values based on selection

Fri, 30 Sep 2016 13:14:57 GMT

Hi Antonio,

It is slimier to my requirement but i dont want to create month i want to use date only,but when i replaced month with date1 it is not working.

if possible can you change the code with date it self .

Thanks in advance

Thiru

Re: highlet highest and lowest values based on selection

Fri, 30 Sep 2016 13:17:55 GMT

Hi Sunny,

I am not expecting filter records,i need to show all records

and

i want ti high let with some back ground color based on selected months

for exapmle

5 months

Date Location Sales Growth Month_No

2016-01-01 Delhi 10000 -10000 9

2016-02-01 Delhi 15000 50% 8

2016-03-01 Delhi 20000 33% 7

2016-04-01 Delhi 10000 -50% 6

2016-05-01 Delhi 30000 200% 5

2016-06-01 Delhi 45000 50% 4

2016-07-01 Delhi 60000 33% 3

2016-08-01 Delhi 50000 -20% 2

2016-09-01 Delhi 100000 100% 1

i highleted 200 and -20(max and min)

3 months

Date Location Sales Growth Month_No

2016-01-01 Delhi 10000 -10000 9

2016-02-01 Delhi 15000 50% 8

2016-03-01 Delhi 20000 33% 7

2016-04-01 Delhi 10000 -50% 6

2016-05-01 Delhi 30000 200% 5

2016-06-01 Delhi 45000 50% 4

2016-07-01 Delhi 60000 33% 3

2016-08-01 Delhi 50000 -20% 2

2016-09-01 Delhi 100000 100% 1

i highleted 100 and -20(max and min)

Thanks

Thiru

Re: highlet highest and lowest values based on selection

antoniotiman — Fri, 30 Sep 2016 13:25:59 GMT

I need Month to simplify expressions.

See new doc

Re: highlet highest and lowest values based on selection

sunny_talwar — Fri, 30 Sep 2016 13:34:28 GMT

Check now

Re: highlet highest and lowest values based on selection

antoniotiman — Fri, 30 Sep 2016 13:42:20 GMT

See Attachment w/o Month (only Date)

Re: highlet highest and lowest values based on selection

Fri, 30 Sep 2016 13:52:31 GMT

Hi Sunny,

Growth here direct column but actually it is a expression

(Curr_value-Prev_value)/Prev_Vlaue

In your expression your directly used Growth as column can you please modify with expression

Thanks

Thiru

Re: highlet highest and lowest values based on selection

sunny_talwar — Fri, 30 Sep 2016 13:54:23 GMT

So the idea is right, you just want to replace the Growth with an expression? Would you be able to share your exact expression?

Re: highlet highest and lowest values based on selection

Fri, 30 Sep 2016 14:01:37 GMT

Hi Sunny,

Yes

My expression is

(Sales-Prev_sales)/Sales

Prev_sales i have created in script using peek

Thanks

Thiru

Re: highlet highest and lowest values based on selection

sunny_talwar — Fri, 30 Sep 2016 14:11:13 GMT

May be try this as your expression

=Only({1}(Sales-Prev_sales)/Sales)

Background expression:

=If(Only((Sales-Prev_sales)/Sales) = Max(TOTAL {<Date = {"$(='>' & Date(AddMonths(Max(Date), -Max(MonthFilter))) & '<=' & Date(Max(Date)))"}>}(Sales-Prev_sales)/Sales), Green(),

If(Only((Sales-Prev_sales)/Sales) = Min(TOTAL {<Date = {"$(='>' & Date(AddMonths(Max(Date), -Max(MonthFilter))) & '<=' & Date(Max(Date)))"}>}(Sales-Prev_sales)/Sales), Red()))

Re: highlet highest and lowest values based on selection

Mon, 03 Oct 2016 07:32:08 GMT

Hi Sunny,

please find the below attachments for smaple data which i am using.

Please modify it and do the needful

Thanks

Thiru

Re: highlet highest and lowest values based on selection

Mon, 03 Oct 2016 08:04:19 GMT

Hi Antonio,

Your script is working fine.

But i have few modications in that

1.I have forecast data also in my actual application. so i can't use directly max(date) because it will take my forecast max date so i have created a variable for my maximum date which is in databbase

i.e $((V_DB_Max_date))

your code

If(Max({<Date={'>=$(=MonthStart(Max(Date),-vInput))'}>} TOTAL Aggr(Growth,Date))=Growth,lightGreen(),

If(Min({<Date={'>=$(=MonthStart(Max(Date),-vInput))'}>} TOTAL Aggr(Growth,Date))=Growth,lightRed()))

My code

If(Max({<Yearrrr={'>=$(=MonthStart($(V_DB_Max_date),-Base_Growth))'}>} TOTAL Aggr(column(4),Yearrrr))=column(4),lightGreen(),

If(Min({<Yearrrr={'>=$(=MonthStart($(V_DB_Max_date),-Base_Growth))'}>} TOTAL Aggr(column(4),Yearrrr))=column(4),lightRed()))

Please modify according this

Thanks

Thiru

Re: highlet highest and lowest values based on selection

antoniotiman — Mon, 03 Oct 2016 09:20:59 GMT

See Attachment

Regards,

Antonio