Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
IAMDV
Luminary Alumni
Luminary Alumni

*** 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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

11 Replies
Miguel_Angel_Baeyens

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
Luminary Alumni
Luminary Alumni
Author

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

Not applicable

Hi

Use this expression

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

IAMDV
Luminary Alumni
Luminary Alumni
Author

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
Partner - Master
Partner - Master

Hi, DV

That expression should work...

Please, check my attachment.

Best Regards,

Erich

deepakk
Partner - Specialist III
Partner - Specialist III

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.

Not applicable

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

Not applicable

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

IAMDV
Luminary Alumni
Luminary Alumni
Author

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