Set Analysis - Calculation with a Variable

[cpiocpio]

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 ??

[cpiocpio]

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

[JonnyPoole]

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'

[cpiocpio]

I will try it later thanks and advise

Mike

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

[cpiocpio · Accepted solution]

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

[JonnyPoole]

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)

[cpiocpio]

Yes, that is what I have done

Thanks

Mike