topic Re: varuavle and set analysis in QlikView

varuavle and set analysis

adiarnon — Mon, 06 Mar 2017 07:01:11 GMT

hi,

in my script i calaulate a variable that have -

NUM((Sum({<YearMonth={">$(v_from_date)"},COMPONENETID={"49"}>}COMPONENTVALUE)

and my variable v_from_date= =date(AddYears(MakeDate(max(Year),max(Month),1),-1),'YYYYMM')

it not working!!

i need the sum of component value when YearMonth>date(AddYears(MakeDate(max(Year),max(Month),1),-1),'YYYYMM') andCOMPONENETID={"49"}>}

how can i get it?

adi

Re: varuavle and set analysis

lironbaram — Mon, 06 Mar 2017 08:25:03 GMT

if you want to calculate the value in the script

then you'll need to run a query on the loaded table

something like :

Sum:

load sum(COMPONENTVALUE) AS Sum_COMPONENTVALUE

resident Data

where COMPONENETID = 49 and YearMonth > addyears(max(YearMonth),-1)

and then you can assign the result to a variable

let vSum = peek('Sum_COMPONENTVALUE',0,'Sum');

Re: varuavle and set analysis

adiarnon — Mon, 06 Mar 2017 08:33:23 GMT

its not what I meant

i need that the expression will Calculated on the fly and not in the script

in the script i define the expression of the variable-

Sum({<YearMonth={">$(v_from_date)"},COMPONENETID={"49"}>}COMPONENTVALUE)

but the v_from_date is depend on the selection of the user

Re: varuavle and set analysis

lironbaram — Mon, 06 Mar 2017 08:48:52 GMT

try using those variables

let vSum= 'NUM(Sum({<YearMonth={">$' & '(v_from_date) <=$' & '(=max(YearMonth))"},COMPONENETID={"49"}>}COMPONENTVALUE))';

set v_from_date= '=date(AddYears(max(YearMonth),-1))';

i added a restriction that it will calculate 12 months period to max selected date

Re: varuavle and set analysis

adiarnon — Mon, 06 Mar 2017 09:37:56 GMT

tnx its help,

but i still have a problem

if im just writing an expression-

Sum({<YearMonth={">$(=date(AddYears(MakeDate(max(Year),max(Month),1),-1),'YYYYMM'))"},COMPONENETID={"49"}>}COMPONENTVALUE)

i get the right numbet

i have a variable

the have -

Sum({<YearMonth={">$=date(AddYears(MakeDate(max(Year),max(Month),1),-1),'YYYYMM')"},COMPONENETID={"49"}>}COMPONENTVALUE)

the different is ( )

but when im try to insert the () the variable get

Sum({<YearMonth={">

whats the problem?

Re: varuavle and set analysis

Anonymous — Mon, 06 Mar 2017 10:08:58 GMT

hi,

You Can try out this expression :

Sum({< COMPONENT_ID={"49"}, YearMonth={">= $(=date(AddYears(MakeDate(max(YEAR),max(MONTH),1),-1)) "}>}COMPONENT_VALUE)

Re: varuavle and set analysis

lironbaram — Mon, 06 Mar 2017 11:15:51 GMT

the problem

is that Qlik sees the $ signs and try to calculate the part after the $ sign as a variable

change your variable expression in the script to

let vExpression ='Sum({<YearMonth={">$' & '=date(AddYears(MakeDate(max(Year),max(Month),1),-1),'YYYYMM')"},COMPONENETID={"49"}>}COMPONENTVALUE)';

this will build the right expression inside the variable

Re: varuavle and set analysis

sasiparupudi1 — Mon, 06 Mar 2017 12:34:23 GMT

Try

Let x='Sum({<YearMonth={">$(=date(AddYears(MakeDate(max(Year),max(Month),1),-1),'YYYYMM'))"},COMPONENETID={49}>}COMPONENTVALUE)';

hth

Sas