topic Re: Multi selections in QlikView

Multi selections

Chanty4u — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i need small help , i am caluclating the YOY growth in stright table so i have a list box with values like 2017Q1 to 2018Q3 so the expression which i used is working fine with single selection but when i tried to select multiple values the result is showing null ... how to avoid this ? below is my expresion

HW YoY Growth : =IF(Quarter = vNewMaxQtr,(($(vHWFCYOYServ)- $(vHWRevYOYServ)) / $(vHWRevYOYServ)), (($(vHWACTYOYServ)-$(vHWRevYOYServ)) / $(vHWRevYOYServ))

Current Quarter: HW Forecast / HW Revenue

**********************************************************************************************************************************************************************************

vHWFCYOYServ = if(max(Dimensionality())=1,

sum({$<Quarter={$(vCurQtr)},Service_PL={'HWR'},Type={'HW Forecast SR'}>}Service_Amount),

if(max(Dimensionality())=2,

sum({$<Quarter={$(vCurQtr)},Service_PL={'HWR'},Type={'HW Forecast SSR'}>}Service_Amount),

if((Dimensionality())=0,

sum({$<Quarter={$(vCurQtr)},Service_PL={'HWR'},Type={'HW Forecast SR'},Sub_Region=>}Service_Amount)+sum

({$<Quarter={$(vCurQtr)},Service_PL={'HWR'},Type={'HW Forecast SSR'},sub_sub_region=>}Service_Amount))))

vHWRevYOYServ = Sum({$<Quarter={$(vPrevQtr)},Type={'HW Actuals'}>}Hardware_Revenue_Amt)

can anyone have idea please help on this?

thanks

Chanty

Re: Multi selections

qlikviewwizard — Fri, 20 Jul 2018 07:13:04 GMT

Upload some sample data with the qlikview file.

Re: Multi selections

jonathandienst — Fri, 20 Jul 2018 07:14:00 GMT

With information that you have provided its pretty hard to give informed advice.

I recommend that you upload a small sample with the table, your variables and expressions and sufficient data to populate the table. It does not have to be real data, but it must contain the structure.

As always with expressions with nested variables, its best to try and get the expression to work with out the variables first and then replace parts of the expression with the variables one a a time. That way your are solving a single problem and you have a working table as a starting point.

Re: Multi selections

Chanty4u — Fri, 20 Jul 2018 10:15:11 GMT

Here is my varaible values for

vCurQtr =chr(39) & RIGHT(Quarter, 6) & chr(39)

vPrevQtr =chr(39)&(left(RIGHT((Quarter), 6), 4)-1)& Right(RIGHT((Quarter), 6), 2)&chr(39)

in above variables how can i include for multiple selections ? loveisfail‌

Petter Skjolden

Re: Multi selections

felipedl — Fri, 20 Jul 2018 11:21:03 GMT

Hi Chanty,

Attached the QVW and a pic i've mentioned.

Hope it helps.

Re: Multi selections

Chanty4u — Fri, 20 Jul 2018 11:21:49 GMT

thanks Felip let me check and update you

Re: Multi selections

felipedl — Fri, 20 Jul 2018 11:22:47 GMT

Sure no problem, hope this one helps you .

Re: Multi selections

felipedl — Fri, 20 Jul 2018 11:28:42 GMT

Chanty,

I've put a wrong chr(39) char on the string, the expressions should be this:

vCurQtr: =chr(39)&concat(Quarter,chr(39)&','&chr(39))& chr(39)

and

vPrevQtr: =chr(39)&concat((Left(Quarter,4)-1)&Right(Quarter,2),chr(39)&','&chr(39)) & chr(39)

So it gets all values encapsulated in the quotes, like so:

Re: Multi selections

Anil_Babu_Samineni — Fri, 20 Jul 2018 11:45:59 GMT

The best way can using this as well.

vCurQtr = Concat(DISTINCT Chr(39) & RIGHT(Quarter, 6) & Chr(39),',')

vPrevQtr = Concat(DISTINCT Chr(39) & (left(RIGHT(Quarter, 6), 4)-1)& Right(RIGHT(Quarter, 6), 2)) & Chr(39),',')

Re: Multi selections

Chanty4u — Fri, 20 Jul 2018 12:09:05 GMT

thanks anil. Felip expression is working fine. let me check yours aswell.

Re: Multi selections

YoussefBelloum — Fri, 20 Jul 2018 12:12:55 GMT

I recommend to add Distinct inside Concat() to be sure to have unique values in case you select multiple values..

Re: Multi selections

Chanty4u — Fri, 20 Jul 2018 12:19:23 GMT

you mean this?

or where i need to have the distinct?

vCurQtr: =chr(39)&concat(distinct Quarter,chr(39)&','&chr(39))& chr(39)

and

vPrevQtr: =chr(39)&concat(distinct(Left(Quarter,4)-1)&Right(Quarter,2),chr(39)&','&chr(39)) & chr(39)

Re: Multi selections

YoussefBelloum — Fri, 20 Jul 2018 12:22:46 GMT

wherever you need to use concat to be able to put multiple values inside set analysis for example

No need to have duplicated values (it can cause problems)

So on both expression (don't know which one will work).

Re: Multi selections

Chanty4u — Fri, 20 Jul 2018 12:23:55 GMT

Thanks Youssef. first let me validate with the data if i got anything wrong will try as you suggested.

Re: Multi selections

Anil_Babu_Samineni — Fri, 20 Jul 2018 14:01:08 GMT

That' fine - Please close this thread