issue in expression

[Chanty4u]

Hi all,   @sunny_talwar

i  have one variable like below.

=vPrevQtr

=chr(39)&concat(DISTINCT(Left(Quarter,4)-1)&Right(Quarter,2),chr(39)&','&chr(39)) & chr(39)

i have used  vPrevQtr in text object  its working fine its giving exact result..    but same varibale  used in  below expression its giving null.  As we have data for that   [its shwoing Expression is OK ]

if(GetFieldSelections(ServiceGroup) = '9R', Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals'}>}Service_Amount),
if(WildMatch(getfieldselections(ServiceGroup),'*9R*'),Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals','SKU'}>}Service_Amount),
Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals'}>}Service_Amount)))

 where im doing wrong>?

Thanks

[Chanty4u]

added = in the variable and used ur expression also same "0 " am getting the result.

[rubenmarin]

In that case I will go from simpler expression to more complex ones, and checking when the value is changing to zero...

First try:
Sum(Service_Amount)

2nd try (If first try it's not zero):
Sum({$<Quarter={$(vPrevQtr)}>}Service_Amount)

3rd try (If 2nd try it'0s not zero):
Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals'}>}Service_Amount)

This is to isolate the set analysis that is removing access to data, once isolated you can focus in that step to solve the problem.

[Chanty4u]

Hi Rubenmarin 

sample data is attached .  and script is used to create year and halfyear,quarter

Data:

load Distinct
Quarter,
left(Quarter,4) as Year,
If(Right(Quarter, 1) < 3, LEFT(Quarter,4) & 'H1', LEFT(Quarter,4) & 'H2') as Half_Year;

Text box expresion:

=if(GetFieldSelections(ServiceGroup) = '9R', Sum({$<Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount),
if(WildMatch(getfieldselections(ServiceGroup),'*9R*'),Sum({$<Quarter={$(=vPrevQtr)},Type={'Actuals','SKU'}>}Service_Amount),
Sum({$<Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount)))

Filter selection in front end:

Half year  only.

[Chanty4u]

i Rubenmarin

i have followed your 3 steps still 0    you can see attached screenshot

[rubenmarin]

Ok, I haven't read about the half_year... that's because selections in half_year is from current year, and quarter set analysis is on last year, so there are no values with both conditions, try removing selection of half_year in set analysis:
=if(GetFieldSelections(ServiceGroup) = '9R', Sum({$<Half_Year, Year, Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount),
if(WildMatch(getfieldselections(ServiceGroup),'*9R*'),Sum({$<Half_Year, Year, Quarter={$(=vPrevQtr)},Type={'Actuals','SKU'}>}Service_Amount),
Sum({$<Half_Year, Year, Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount)))

Edit: Same for Year

[HirisH_V7]

Check the attached.

[Chanty4u]

thanks. im getting some values now. i need to implement this into other 4,5 variables and validate the data and come back to you.