topic Re: Set Analysis using Field Value in QlikView

Set Analysis using Field Value

Mon, 03 Nov 2014 15:52:31 GMT

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.

Re: Set Analysis using Field Value

dickelsa — Mon, 03 Nov 2014 15:57:45 GMT

see post below:

Re: How to specify explicit > and < in set modifier

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

Regards,

Dick

Re: Set Analysis using Field Value

morganaaron — Mon, 03 Nov 2014 16:01:51 GMT

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

Re: Set Analysis using Field Value

MK_QSL — Mon, 03 Nov 2014 16:09:53 GMT

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

Re: Set Analysis using Field Value

Mon, 03 Nov 2014 16:16:52 GMT

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

Re: Set Analysis using Field Value

Mon, 03 Nov 2014 16:25:09 GMT

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])

Re: Set Analysis using Field Value

Mon, 03 Nov 2014 16:29:01 GMT

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])

Re: Set Analysis using Field Value

satishkurra — Mon, 03 Nov 2014 16:33:56 GMT

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

Expression:

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

Re: Re: Set Analysis using Field Value

Mon, 03 Nov 2014 19:33:26 GMT

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!

Re: Re: Set Analysis using Field Value

Mon, 03 Nov 2014 21:03:28 GMT

Hello Carlo,

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

Thanks for your help!

Re: Set Analysis using Field Value

Tue, 04 Nov 2014 08:17:32 GMT

Hi L R.

Thank you for the attached file.

Look at my reply, your calculation is performed in two steps. This makes it less complex.

Step1 - differs records using conditional Type

Step2 - insert distinct Type in your set analysis:

Prior Calculation: Avg({<Type={'Prior'},[Issued in Exchange]={N}>}[Total Doc Amount])

Post Calculation: Avg({<Type={'Post'},[Issued in Exchange]={N}>}[Total Doc Amount])

This is a great way to perform conditional and f calculations.

I hope that will help you!

Re: Re: Re: Set Analysis using Field Value

Tue, 04 Nov 2014 08:21:39 GMT

Here's the example

Re: Re: Set Analysis using Field Value

Tue, 04 Nov 2014 16:24:31 GMT

Hello Carlo,

Thank you so much. That's exactly what I needed.