Set analysis with variables

[#1]

Hi there,

I have a variable for total sales: vTotalSales.

I'd like to know how to perform a set analysis on this variable, say restricting it to a year or particular country.

Right now, to display sales figures in a chart table I would write the following expression: =$(vTotalSales)

How could I modify this to show only sales for, say, 2015? Nothing I've done so far seems to work.

Thanks,

Andrew

[alexandros17]

The variable contains only a value, so you can restrict a field to a variable value, not a variable to a value

[julian_rodriguez]

Hello Andrew,

You can't just "inject" set analysis into a variable.

One approach could be:

- A variable for the set analysis sentence: vSetModifier = '{$<Year = {2015}>}'

- A variable for the expression like: vTotalSales = Sum( $(vSetModifier) Sales)

If vSetModifier = '' or null, the expression will work without any set analysis, else, will insert the set analysis modifier.

I hope this be useful for you...

Regards

[Anonymous]

Hi

vTotalSales=Sum({<YearField={2015}>}FieldSales) This you return all  sales into 2015

vTotalSales= Sum({<YearField={2015},FieldCountry={'France'}>}FieldSales) This you return all  sales into 2015  but  only France

Regards

[#5]

Thanks for the answers.

I guess the issue is that FieldSales field isn't being used. My developers have calculated net sales and put it into the variable above and this is being used instead of FieldSales.

Andrew

[#6]

Let's say the variable vTotalSales definition is Sum(Sales). Change the definition to Sum($1 Sales).

In the chart expression write something like below

$(vTotalSales({<Year={2015}>}))

Let us know the result. Let me know if you need any help.

Regards,

KKR

[#7]

Hi KKR,

The issue seems to be with the way my IT guys have created the variable - it doesn't use the Sales field. It's something like this (not exact):

vTotalSales = sum((UnitsUnitPrice)ExchangeRate - (Discounts+ShippingIncome))

Andrew