Creating a variable in set analysis

[krissy_s]

Hiya

I have the following set expression (in a table):

=Aggr (Only({<[Expiry Date] = {">=$(=monthstart(Today()))<=$(=monthend(addmonths(today(),12)))"}>} [Expiry Date]),[Policy Number])

I need to create a variable in this set expression above:

 [Expiry Date] = {"$(=vExpiryRange)

Does anyone know to add this in please?

Many thanks

[paoloderegibus]

Hi Kristen,

you could try this:

in the script create this variable:

LET vExpiryRange = replace('"'&'>=@(=monthstart(Today()))<=@(=monthend(addmonths(today(),12)))'&'"','@','$');

and in charts use this dimesnion:

=Aggr(Only({<[Expiry Date] = {$(vExpiryRange)}>} [Expiry Date]),[Policy Number])

See the attached qvf.

Ciao

Paolo

[krissy_s]

thank you so much! Will give it a go!

[krissy_s]

Sorry, is it possible to write in the expression without having to edit the script?

Many thanks!

[paoloderegibus]

Sure, just create the variable and paste this in its expression: '"'&'>=$(=monthstart(Today()))<=$(=monthend(addmonths(today(),12)))'&'"'.