Set Analysis using Field Value

[#1]

I am very new to QV and set analysis.  I am trying to do a calculation based on a field value.  The statement I currently have

Avg({<[Issue Date]={'<=10/27/14'},[Exchg]={N}>}[Amount])  works with a hard coded date (10/27/14).  What I want to do is replace the date with a field (MyField) from my table that has a moving date.  In summary, I need to perform this calculation when Issue Date <= MyFieldValue

Anyone know how I can achieve this?

Thanks in advance.

[dickelsa]

see post below:

Re: How to specify explicit &gt; and &lt; in set modifier

also, you should put {N} as {'N'}

Regards,

Dick

[morganaaron]

Try: Avg({<[Issue Date]={"<=$(=max(Date(MyField)))"}, Exchg={'N'}>}Amount) if you want it to be less than a date selected in "MyField".

[MK_QSL]

Avg({<[Issue Date]={'<=$(=Max([Issue Date]))'},[Exchg]={'N'}>}[Amount])

[#5]

If you could attach a sample file I could solve your problem.

[#6]

I think the best way to handle this problem is to create a variable directly in load script and then use it in your set expression.

For example,

Load Script:

vMaxDate = Date(Peek('MyField',0,'YourTableNameInQlik'))

Set Expression:

Avg({<[Issue Date]={"<=$(vMaxDate)"},[Exchg]={'N'}>}[Amount])

[#7]

Sorry, I forgot "LET" statement, try this:

Load Script:

LET vMaxDate = Date(Peek('MyField',0,'YourTableNameInQlik'))     //replace table name with your table name

Set Expression:

Avg({<[Issue Date]={"<=$(vMaxDate)"},[Exchg]={'N'}>}[Amount])

[satishkurra]

Hi

LET vDate = Date#(Peek('FieldName',0,'TableName'))   

Expression:

Avg({<[Issue Date]={"<=$(vDate)"},[Exchg]={'N'}>}[Amount])

[#9]

I've put together an example of what I'm looking to do.

The attached xls has sample data.  I've calculated to the right what the Prior and Post Columns in QV should reflect.  If the Issue Date <= ValidateDate it goes in Prior Bucket, if the Issue Date > ValidateDate it goes in Post Bucket.

Thanks for your help!

[#10]

Hello Carlo,

I've put together an example of what I'm looking to do.

The attached xls has sample data.  I've calculated to the right what the Prior and Post Columns in QV should reflect.  If the Issue Date <= ValidateDate it goes in Prior Bucket, if the Issue Date > ValidateDate it goes in Post Bucket.

Thanks for your help!