topic Storing Set Analysis to Variable in QlikView

Storing Set Analysis to Variable

Fri, 02 May 2014 14:05:46 GMT

I have an expression that calculates the amount per Report Date.

Sum({$<%Report_Date_Id = {$(=Max(%Report_Date_Id))}, Year = , Quarter = , Month = >} [Amount])

I would like to save the set analysis expression (just the point in time expression) into a variable since I will need it in multiple places in the document and possibly on other documents too. I tried the following but none of them works

set vCurrentPeriod = %Report_Date_Id = {$(=Max(%Report_Date_Id))}, Year = , Quarter = , Month =;

set vCurrentPeriod = '%Report_Date_Id = {$(=Max(%Report_Date_Id))}, Year = , Quarter = , Month ='

My goal is to be able to do this on the expression:

=Sum({$<$(vCurrentPeriod)>} [Amount])

Any help is greatly appreciated.

Re: Storing Set Analysis to Variable

Gysbert_Wassenaar — Fri, 02 May 2014 14:12:12 GMT

Try

LET vCurrentPeriod = '%Report_Date_Id = {$' & '(=Max(%Report_Date_Id))}, Year = , Quarter = , Month =';

Re: Storing Set Analysis to Variable

preminqlik — Fri, 02 May 2014 14:18:28 GMT

press ctrl+alt +v

and create variable over there and doesnot give = sigh before

=%Report_Date_Id = {$(=Max(%Report_Date_Id))}, Year = , Quarter = , Month = -->>>> wrong method

%Report_Date_Id = {$(=Max(%Report_Date_Id))}, Year = , Quarter = , Month = ------>>Correct method

now u can use

Sum({$<$(vCurrentPeriod)>} [Amount])

Re: Storing Set Analysis to Variable

Fri, 02 May 2014 15:15:23 GMT

Thanks Kumar I was able to make this work using this approach. I wanted to save this in a .qvs file so I'll have to do the assignment in script.

Re: Storing Set Analysis to Variable

Fri, 02 May 2014 15:26:04 GMT

Thanks Gysbert Wassenaar this solved my problem. I did notice that when I was using the SET, the value stored when I look at the variable overview screen is different than what I assigned in the script. It looks like this...

vCurrentPeriod = '%Report_Date_Id = {}, Year = , Quarter = , Month =

I don't know what's happening but it is dropping the dollar expansion expression inside the curly braces. But using LET and breaking it into 2 strings somehow worked. Would you explain the logic behind that?

Re: Storing Set Analysis to Variable

preminqlik — Fri, 02 May 2014 15:28:22 GMT

then once try this

set vCurrentPeriod = '%Report_Date_Id = {$(=Max(%Report_Date_Id))}, Year = , Quarter = , Month =' ;

or else

write in excel and peek it from there

Re: Re: Storing Set Analysis to Variable

IAMDV — Fri, 02 May 2014 15:33:36 GMT

Hi,

You can use ASCII values along with LET statement. Something like this...

	LET vMyExpression = 'Sum({<%Report_Date_Id = {' & Chr(36) & '(=Max(%Report_Date_Id))}, Year = , Quarter = , Month = >} [Amount])';

Thanks,

www.QlikShare.com

Re: Storing Set Analysis to Variable

Fri, 02 May 2014 15:40:38 GMT

I had this originally but it does not work.

set vCurrentPeriod = '%Report_Date_Id = {$(=Max(%Report_Date_Id))}, Year = , Quarter = , Month =' ;