11 Replies Latest reply: Jun 8, 2011 8:11 AM by Sridhar Ethiraj

# *** Yet another Set Analysis Question ***

Dear All,

I have quick question on one of the expressions. Here is the expression...

=SUM({1<FYrNum = {\$(=Only(FYrNum)-1)}, StoreName = {"*"}>} NetSales) / 1000000

I am sure this should be pretty simple to solve... but I am struggling to get my head around this one.

I have two list boxes : FYrNum & StoreName

If the user selects 2010 in FYrNum (Listbox) I wanted to show the data for previous year, however user will also filter for the StoreName. My Current expression (above stated) doesn't filter on StoreName because we are using {1} and it just shows the data for all the stores.

So I need the data for FYrNum when the user selects the years and if the users selects the StoreName I wanted the data to be filtered on StoreName as well. I am using all this in one Guage chart.

I think I need to use SET OPERATOR to get this working but I am struggling to get this working.

Many thanks in anticipation.

Cheers - DV

• ###### Re: *** Yet another Set Analysis Question ***

Hello,

If I got your question right, you want your gauge chart to be affected by selections in both FYrNum and StoreName. Is that right? Then I'd use something like

```SUM({< FYrNum = {\$(=Only(FYrNum)-1)} >} NetSales) / 1000000
```

Is this what you are expecting?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: *** Yet another Set Analysis Question ***

Miguel - Thank you for quick reponse. You got the question right... however, I tried your expression and it returns 0 instead of the previous year's NetSales. I am assuming that is because we are using current selection. Please help...

Thanks - DV

• ###### *** Yet another Set Analysis Question ***

Hi

Use this expression

SUM({\$<FYrNum = {\$(=Only(FYrNum)-1)}>} NetSales) / 1000000

• ###### Re: *** Yet another Set Analysis Question ***

Bhaskar - This is same exactly same what Miguel has given. Even if you dont place the '\$' at the beginning QV assumes it as current selection. So this is exactly same as Miguel's solution. The problem with this approach is... if I select FYrNum (Year) as 2010 then my current selection is for 2010 then how would get the data for previous year?

Hope this makes sense...

Cheers - DV

• ###### Re: *** Yet another Set Analysis Question ***

Hi, DV

That expression should work...

Best Regards,

Erich

• ###### Re: *** Yet another Set Analysis Question ***

hi Deepak,

check what's New in 9.0 Template (Dashboard tab). You will find the solution to your problem. Use the same syntax which they have used.

Sorry the copy past is not woking for me.

• ###### Re: *** Yet another Set Analysis Question ***

Dear All – Folks it works and it was my mistake as I amfiltering on different Calendar Year (Names are so similar). Many thanks andsorry for inconvience.

Cheers - DV

• ###### Re: *** Yet another Set Analysis Question ***

Hi Mate,

Not sure i have understood your problem.

Have a look at the attached application.

-Sridhar

EDIT : I`m too late to post this

• ###### Re: *** Yet another Set Analysis Question ***

Sridhar, Erika & Bhaskar – Thanks for the suggestions. Iwas filtering on wrong field and spent 2 hours since morning

• ###### Re: *** Yet another Set Analysis Question ***

Mate, It happens some time for me also noworries.Cheers

• ###### *** Yet another Set Analysis Question ***

Hope 'FYrNum'  is in Number Format and Not Text format..