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

[MK_QSL]

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

[JonnyPoole]

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 ?

[luciancotea]

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.

[cpiocpio]

Hi

Thanks for reply same results this yr work

But last year does not

[cpiocpio]

Afraid not, no totals returne

[JonnyPoole]

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

[cpiocpio]

Thanks but no joy

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

[cpiocpio]

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

[JonnyPoole]

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

is it

=<something>

or

<something>

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