Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Chanty4u
MVP
MVP

Multi selections

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

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample.png

View solution in original post

14 Replies
qlikviewwizard
Master II
Master II

Upload some sample data with the qlikview file.

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Chanty4u
MVP
MVP
Author

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

felipedl
Partner - Specialist III
Partner - Specialist III

Hi Chanty,

Attached the QVW and a pic i've mentioned.

Hope it helps.sample.png

Chanty4u
MVP
MVP
Author

thanks Felip let me check and update you

felipedl
Partner - Specialist III
Partner - Specialist III

Sure no problem, hope this one helps you .

felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample.png

Anil_Babu_Samineni

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),',')

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Chanty4u
MVP
MVP
Author

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