*** Yet another Set Analysis Question ***

[IAMDV]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[IAMDV]

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

[#4]

Hi

Use this expression

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

[IAMDV]

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

[erichshiino]

Hi, DV

That expression should work...

Please, check my attachment.

Best Regards,

Erich

[deepakk]

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.

[#8]

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

[#9]

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

[IAMDV]

Miguel – You are absolutely right!! Your expression worksand I had unnecessarily made things complicated. I am kicking myself for notthinking about this… its my mistake

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