varuavle and set analysis

[adiarnon]

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

[lironbaram]

hi

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

[adiarnon]

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

[lironbaram]

hi

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

[adiarnon]

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?

[Anonymous · Accepted solution]

hi,

You Can  try out this expression :

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

[lironbaram]

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

[sasiparupudi1]

Try

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

hth

Sas