topic Re: Set Analysis - Calculation with a Variable in QlikView

Set Analysis - Calculation with a Variable

cpiocpio — Wed, 11 Mar 2015 16:35:30 GMT

I have a set analysis as follows that returns a set of records for the value of variable vFY.

=if(Vcurr='STG',

num(Sum ({<FY={$(=vFY)}>}

val_base), '£#,##0.00'),

num(Sum ({<FY={$(=vFY)}>}

val_eur),'€#,##0.00'))

I want to have similar SA but it returns data for vFY less one, so if vFY is 2014, it should return 2013 records.

I can place the following into text objects and they return the correct value

='Value is = ' & $(=vFY) Returns 2014

='Value is = ' & $(=vFY-1) Returns 2013

So using $(=vFY) in SA works

but $(=vFY-1) does not ??

Re: Set Analysis - Calculation with a Variable

MK_QSL — Wed, 11 Mar 2015 16:52:29 GMT

=if(Vcurr='STG',

num(Sum ({<FY={'$(=vFY)'}>}val_base), '£#,##0.00'),

num(Sum ({<FY={'$(=vFY)'}>}val_eur),'€#,##0.00'))

and

=if(Vcurr='STG',

num(Sum ({<FY={'$(=vFY-1)'}>}val_base), '£#,##0.00'),

num(Sum ({<FY={'$(=vFY-1)'}>}val_eur),'€#,##0.00'))

Re: Set Analysis - Calculation with a Variable

JonnyPoole — Wed, 11 Mar 2015 16:56:46 GMT

it should work

Sum ({<FY={$(=vFY-1)}>} val_eur) -> should be the same as Sum ({<FY={2013}>} val_eur)

you could try something like the following to ensure numbers are being compared

Sum ({<FY={$(=num#(vFY-1))}>} val_eur)

Or you want to post a sample qvw ?

Re: Set Analysis - Calculation with a Variable

luciancotea — Wed, 11 Mar 2015 16:57:23 GMT

It works, you just have to match the data type.

For example, if your variable is string '2014' it will return a string, while (vFY-1) will auto cast the result to an integer.

Re: Set Analysis - Calculation with a Variable

cpiocpio — Wed, 11 Mar 2015 17:26:43 GMT

Thanks for reply same results this yr work

But last year does not

Re: Set Analysis - Calculation with a Variable

cpiocpio — Wed, 11 Mar 2015 17:29:43 GMT

Afraid not, no totals returne

Re: Set Analysis - Calculation with a Variable

JonnyPoole — Wed, 11 Mar 2015 17:39:15 GMT

hmmm.. maybe the num#() has to go around just the vFY but that would be unusual since the '-' should be enough to accommodate things.

Sum ({<FY={$(=num#(vFY)-1)}>} val_eur)

wrap it all in text ?

Sum ({<FY={$(=text(num#(vFY)-1))}>} val_eur)

not 100% sure... if you can post a QVW that will help the members bang out a solution for you quickly

Re: Set Analysis - Calculation with a Variable

cpiocpio — Wed, 11 Mar 2015 17:48:55 GMT

Thanks but no joy

I will have to try and make a sample, full file is massive

Re: Set Analysis - Calculation with a Variable

cpiocpio — Wed, 11 Mar 2015 17:50:07 GMT

I assume to change the variable to a integer it is just a case of wrapping Num or Num# around it, neither worked I am sad to say

Re: Set Analysis - Calculation with a Variable

JonnyPoole — Wed, 11 Mar 2015 17:55:07 GMT

last question... what is the definition of the variable vFY ?

is it

=<something>

The latter is used to ensure it calculates at the time the expression where it is used is calculated (and not before)

Re: Set Analysis - Calculation with a Variable

cpiocpio — Wed, 11 Mar 2015 18:03:19 GMT

=FY

It is the value of a “field” FY, that is populated by use of an excel file load and fiel contents link via a field “dated” to the main data object (TABLE)

Re: Set Analysis - Calculation with a Variable

JonnyPoole — Wed, 11 Mar 2015 18:05:41 GMT

if you change it to

=max( distinct FY)

do you get any different behavior ? Using a fieldname by itself can result in multiple records or values and might be the reason for some 'finikiness'

Re: Set Analysis - Calculation with a Variable

cpiocpio — Wed, 11 Mar 2015 18:57:19 GMT

I will try it later thanks and advise

Mike

Sent from my mini iPad, please excuse spelling mistakes, it is the equipment not me

Re: Set Analysis - Calculation with a Variable

cpiocpio — Thu, 12 Mar 2015 08:43:16 GMT

Ok, I think I am getting somewhere,

I think it is not with SA as such that the issue lies, it is the fact that my List Box Selection is conflicting with my SA,

Since the selection is a FY range value e.g. 2011-2011, now with the SAS set to look based on vFY, which has a relationship as follows

Range FY

2011-2012 2011

2012-2013 2012

2013-2014 2013

Then if I pick 2013-2014 as my selection, then the vFY is 2013, AND Vfy IS HAPPY BUT THE the vFY-1 is then 2012, but because Range 2013-2014 is selected, it returns nothing

If I add to SA the statement Range=, then I get data for vFY-1

Mike

Re: Set Analysis - Calculation with a Variable

JonnyPoole — Thu, 12 Mar 2015 14:17:13 GMT

That can happen when there are selectable fields (list boxes) in the UI that will conflict with the context set by the SA statement... exactly right.

Consider cancelling those out with some explicit references like the following:

Sum ({<FY={$(=vFY),Month=,Quarter=,Date=}>} val_base)

Re: Set Analysis - Calculation with a Variable

cpiocpio — Thu, 12 Mar 2015 14:49:52 GMT

Yes, that is what I have done

Thanks

Mike