Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

IAMDV
Honored Contributor II

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

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

11 Replies
MVP
MVP

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

IAMDV
Honored Contributor II

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

Not applicable

*** Yet another Set Analysis Question ***

Hi

Use this expression

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

IAMDV
Honored Contributor II

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

erich_shiino
Honored Contributor

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

Hi, DV

That expression should work...

Please, check my attachment.

Best Regards,

Erich

deepakk
Valued Contributor III

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.

Not applicable

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

Not applicable

*** Yet another Set Analysis Question ***

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

IAMDV
Honored Contributor II

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

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

Community Browser