Concatinating modifyers in a set analysis

[sibrulotte]

Hi,

is it possible to concatinate within a set modifyer. For example, our Year-period field is comprised of 7 characters: 2014-06.

Meaning year 2014, period 6.

I'd like to have a set modifyer that takes the first 5 characters of the selection in "Année-Période financière", for example, it would return "2014-" with the selection of "2014-06", and concatinate that with period 12. This should give the whole year budget in the example below.

sum

({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {left([Année-période financière],5)&'12'} >} [Montant cumulé aad] * %CR004M)

But anyone with skills beyond mine will see that this part:

[Année-période financière]= {left([Année-période financière],5)&'12'}

Doesn't work.

What would?

[swuehl]

Try a dollar sign expansion in your set modifier:

sum

({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {$(=left([Année-période financière],5)&'12') } >} [Montant cumulé aad] * %CR004M)

[sibrulotte]

Hi, thanks for the cue.

The expression is OK,  

sum

({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {$(=left([Année-période financière],5)&'12')} >} [Montant cumulé aad] * %CR004M)

But it returns nulls.

If I type in '2014-12' instead, I get the proper values.

Thanks for the help, I'm still fiddling with the $ expension sign.

[Peter_Cammaert]

I could be incredibly mistaken but I think set analysis still isn't a row-by-row thing (and left() is not an aggregation function). Your new set criteria are determined at the start of object evaluation. IMHO [Année-période financière] on the right is returning all values and left() has difficulty taking out the first 5 characters.

I know it's a lame alternative expression (especially with large data sets) but this one will instantly fix it:

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}>} if (right([Année-période financière], 2) = '12', [Montant cumulé aad] * %CR004M))

I would add a field called Month to your transaction table and use set analysis again with an additional modifier like:

..., [Mois] = {12} >} ...

Am I wrong?

Peter

[swuehl · Accepted solution]

Right, set analysis as well as the dollar sign expansion is only evaluated once per chart, not per dimension value.

I assumed a selection in Année-periode financiére ("...takes the first 5 characters of the selection in ..."), so the expression should work for only a single possible value in this field.

What does

=left([Année-période financière],5)&'12'

return in a text box?

You can also try to enclose this in single quotes, maybe QV is taken this as a calculation otherwise:

sum

({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {'$(=left([Année-période financière],5)&'12')'} >}[Montant cumulé aad] * %CR004M)

[Peter_Cammaert]

BTW happy 40k, S. Nice work.

[sibrulotte]

So the field [Année-Période financière] was meant as a single selection field. All tables are conditionaly calculated with the selection of only one value.

=left([Année-période financière],5)&'12'

returned: 2014-12 in the text box.

and the outside single quotes worked:

[Année-période financière]= {'$(=left([Année-période financière],5)&'12')'}