topic Re: Set analysis with variables in QlikView

Set analysis with variables

Fri, 21 Aug 2015 13:17:16 GMT

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

Re: Set analysis with variables

alexandros17 — Fri, 21 Aug 2015 15:20:56 GMT

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

Re: Set analysis with variables

julian_rodriguez — Fri, 21 Aug 2015 15:22:06 GMT

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

Re: Set analysis with variables

Anonymous — Fri, 21 Aug 2015 15:59:05 GMT

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

Re: Set analysis with variables

Sat, 22 Aug 2015 00:44:33 GMT

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

Re: Set analysis with variables

Sat, 22 Aug 2015 01:41:41 GMT

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

Re: Set analysis with variables

Sat, 22 Aug 2015 03:24:12 GMT

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