topic Re: Showing line based on selection in QlikView

Showing line based on selection

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I want to show line style for ySales based on the selection.

By default it will show max year data and ySales will show dotted line from AUG to DEC as xSales will be zero from SEP.

My requirement is :

When Feb is selected, JAN should show line, and remaining months dotted line.

When Feb is selected, JAN,FEB should show line, and remaining months dotted line.

When Mar is selected, JAN,FEB,MAR should show line, and remaining months dotted line.

By default, JAN it is showing correct. But when I select FEB, JAN data is showing dotted line, FEB line, remaining Months line.

I want JAN,FEB should be dotted line.

I am using this Line Style formula:

If(Month<vMaxDate,'<S1>','<S4>')

Please help me.

Issue:

Requirement:

Load * inline [

Month,xSales,ySales

JAN-16,10,15

FEB-16,20,25

MAR-16,30,35

APR-16,40,45

MAY-16,50,55

JUN-16,60,65

JUL-16,70,75

AUG-16,80,85

SEP-16,,95

OCT-16,,105

NOV-16,,125

DEC-16,,135

];

Re: Showing line based on selection

sunny_talwar — Sat, 08 Oct 2016 11:05:21 GMT

Try this expression for line style:

If(Only({1}Month)<=Max(TOTAL Month),'<S1>','<S2>')

Re: Showing line based on selection

Sat, 08 Oct 2016 12:15:50 GMT

Hi Sunny,

Thank you for the reply.

Please help me for default selection for no Year,Month selection as below:

Re: Showing line based on selection

sunny_talwar — Sat, 08 Oct 2016 12:30:44 GMT

May be this:

If(Only({1}Month)<=Max(TOTAL Month) and Below(Sum({$<Year={$(=max(Year))},Month,MonthName>}xSales)) > 0,'<S1>','<S2>')

No selection

With March selected

Re: Showing line based on selection

Sat, 08 Oct 2016 12:35:02 GMT

I am trying with this . But not working. Please help me.

If(Month<vMaxDate and GetSelectedCount(Year)=0,'<S1>',

if(GetSelectedCount(Year)>0 and Month<vMaxDate ,'<S1>',

If(Only({1}Month)<Max(TOTAL Month),'<S1>',

'<S2>')))

Re: Showing line based on selection

Sat, 08 Oct 2016 12:47:24 GMT

You are great sir

Re: Showing line based on selection

Anil_Babu_Samineni — Sat, 08 Oct 2016 12:47:56 GMT

Does vMaxDate is a variable? If yes, you need to use this

$(=vMaxDate)

Re: Showing line based on selection

sunny_talwar — Sat, 08 Oct 2016 13:41:26 GMT

What is the goal here Jack? I can only interpret your code for the words written in it, what are your trying to do here?

Re: Showing line based on selection

Sat, 08 Oct 2016 14:24:27 GMT

Sunny, your formula is working for the dummy date. Can we try in other way please?

Re: Showing line based on selection

Sat, 08 Oct 2016 14:24:52 GMT

Hi Anil, It is a variable.It is not working

Re: Showing line based on selection

sunny_talwar — Sat, 08 Oct 2016 14:41:20 GMT

How about this:

If(GetSelectedCount(Year) = 0,

If(Month <= AddMonths(MaxDate, -1), '<S1>', '<S2>'),

If(Only({1}Month)<Max(TOTAL Month),'<S1>','<S2>'))

Re: Showing line based on selection

sunny_talwar — Sat, 08 Oct 2016 14:42:41 GMT

Or if you want to use your variable instead of the field, then this:

If(GetSelectedCount(Year) = 0,

If(Month <= AddMonths($(vMaxDate), -1), '<S1>', '<S2>'),

If(Only({1}Month)<Max(TOTAL Month),'<S1>','<S2>'))

Re: Showing line based on selection

jagan — Sat, 08 Oct 2016 14:52:11 GMT

Hi,

Try like this

Variable:

vSelectedDate = Date(If(Max(Date) < vMaxDate, Max(Date), vMaxDate))

Expression:

SUM({$<Year={$(=max(Year))},Month=,MonthName=, Date={'<=$(=vSelectedDate)'}>}xSales) +

SUM({$<Year={$(=max(Year))},Month=,MonthName=, Date={'>$(=vSelectedDate)'}>}ySales)

Background Color Expression:If(Only({<Year={$(=max(Year))},Month=,MonthName=, Date={'<=$(=vSelectedDate)'}>} Date), RGB(0, 255, 0), RGB(0, 0, 255))

Line Style Expression: If(Only({<Year={$(=max(Year))},Month=,MonthName=, Date={'<=$(=vSelectedDate)'}>} Date), '<S1>', '<S2>')

Regards,

Jagan.

Re: Showing line based on selection

jagan — Sat, 08 Oct 2016 14:53:04 GMT

Please find attached file for solution.

Regards,

Jagan.

Re: Showing line based on selection

jagan — Sat, 08 Oct 2016 14:54:42 GMT

Forgot to mention derived a new date field in script.

DATA:

load *,

year(Month) as Year,Month(Month) as MonthName;

Load date#(Month,'MMM-YY') as Month,

Date(date#(Month,'MMM-YY')) as Date,

xSales,ySales inline [

Month,xSales,ySales

JAN-16,10,15

FEB-16,20,25

MAR-16,30,35

APR-16,40,45

MAY-16,50,55

JUN-16,60,65

JUL-16,70,75

AUG-16,80,85

SEP-16,,95

OCT-16,,105

NOV-16,,125

DEC-16,,135

];

SalesDate:

load max(Month) as MaxDate

Resident DATA

where len(xSales)<>0

Order by Month desc;

Let vMaxDate=peek('MaxDate',0,'SalesDate');