How to use variable in Set Analysis

[#1]

Hi All,

I would like to use Variable in Set Analysis:

vStartDate='2015-1-1'

vEndDate='2015-2-1'

How to use it in pivot table expression sum({<YearMonth={'>=vStartDate<vEndDate'}>}revenue) ?

Thanks.

[tresesco]

The field you are comparing with (YearMonth) data format has to match with the variable content. That is what you have to make sure. How does your YearMonth data look like? Which function have you used in the script to create it?

[MayilVahanan · Accepted solution]

Hi

Try like this

=Sum({<YearMonth= {'>=$(vStartDate)<=$(vEndDate)'}>}revenue)

Hope YearMonth filed has same format as in Variables (i.e. YYYY-M-D) If not , please  mention the format for YearMonth Field

[prieper]

In order not to get trapped by different formats, it makes sense to convert the dates to numbers and the make a numerical comparison.

HTH Peter

[#5]

Hi tresesco,

Thanks for your prompt reply, My YearMonth format is the same as the variable like 'YYYY-MM-DD'

[jagan]

Hi,

Try like this

sum({<YearMonth={'>=$(=vStartDate)<$(=vEndDate)'}>}revenue)

Note: YearMonth date values and variable values should be in same format.

Regards,

jagan.

[mayankraoka]

Hi ,

You can use this:

sum({<YearMonth={'>=$(=$(vStartDate))<$(=$(vEndDate))'}>}revenue)

Regards,

Mayank

[tresesco]

Try like:

sum({<YearMonth={'>=$(vStartDate)<$(vEndDate)'}>}revenue)

[Peter_Cammaert]

The basic rule - that you can make out from tres' suggestion and all identical copies down below (?) - is that in a set modifier you specify either exact field values to use, or a "search" string (like when you start typing in a list box). A search string will not handle variables. But in a set modifier it will handle $-sign substitution to translate a variable reference into its actual content.