topic Re. :Re: Re. :Ignore selection without Set Analysis in QlikView

Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 13:49:19 GMT

I need to do a sum() for the current month but ignoring a specific selection. The catch is that I only have QV 8.2. So no Set Analysis.

The thing I have right now is:

SUM(IF([Quotation year] = YEAR(TODAY()) AND [Quotation Month] = MONTH(TODAY()), #quotation_amount))

I have a projecthours date field which I need to ignore in the expression. Don't know if this is even possible in v8.2.

Yes, I know upgrading is the best answer but for a lot of reasons this is no option right now.

Anyone a good idea how to solve this?

Re. :Ignore selection without Set Analysis

martin59 — Fri, 21 May 2010 13:54:56 GMT

Hello,

Have you tried with the 'ALL' or 'TOTAL' qualifiers ? Like that :

sum(ALL ...)

sum(TOTAL ...)

Martin

Re. :Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 14:02:15 GMT

Yes I tried those. No luck with that

Re. :Ignore selection without Set Analysis

boorgura — Fri, 21 May 2010 14:08:37 GMT

Am currently using 8.2.

I use the sum(all <field_names> <expression>) and works absolutely fine.

Sum(all <expression>) ignores all selections and dimensions.

so if you need it to consider certain dimension, then you need to specify those fields (lets say f1 & f2)

for example:

sum(all <f1,f2> Amount)

Re. :Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 14:30:13 GMT

So if I understand you correctly.

f1 & f2 are fields that can be selected by the end user and have effect on my expression. All other fields are ignored?

In my case I have about 30 fields an end-user can make selections on

Re. :Re: Re. :Ignore selection without Set Analysis

martin59 — Fri, 21 May 2010 14:42:58 GMT

f1 & f2 are dimensions of your chart.

This syntax only works in a chart.

Re. :Ignore selection without Set Analysis

boorgura — Fri, 21 May 2010 14:45:04 GMT

No, you got it wrong.

Irrespective of total number of fields - you can create a table with dimension f1,f2 and say just one expresion E1.

now for a given row E1 should correspond to the values F1 and F2 if Sum(ALL ) is not used.

if ALL is used - it ignores the current values in F1 and F2 and any selections made on the remaining fields as well.

Re. :Re: Re. :Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 14:47:01 GMT

Ok,

Then I can not use the solution. The fields I need to ignore are listboxes not in my chart.

Re. :Re: Re. :Ignore selection without Set Analysis

boorgura — Fri, 21 May 2010 14:52:38 GMT

I think I got it wrong now.

So, should a selection on the list box effect the calculations?

Re. :Re: Re. :Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 14:56:47 GMT

No, Selection in my listbox (2 listboxes to be exactly) needs to be ignored in my expression.

Normaly what I would do with Set Analysis is: SUM({$<Month2= >}#amount)

In the example above the field Month2 will be ignored. And Month2 is not a dimension in my chart.

Re. :Re: Re. :Re: Re. :Ignore selection without Set Analysis

martin59 — Fri, 21 May 2010 14:57:01 GMT

So, if you have a chart with 3 dimensions (D1, D2 and D3) and 4 expressions (E1,E2, E3 and E4) :

E1 depends of chart's dimensions and selections

E1 = sum(Value)

E2 depends of chart's dimensions and not selections

E2 = sum(ALL <D1,D2,D3>Value)

E3 depends of your selections and not chart's dimensions

E3 = sum(TOTAL Value)

E4 disregards selections and dimensions

E4 = sum(ALL Value)

Is that helps you ?

Re. :Re: Re. :Re: Re. :Ignore selection without Set Analysis

boorgura — Fri, 21 May 2010 15:13:27 GMT

Hey Martin,

That's a good example.

Thanks man.

Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 15:20:24 GMT

Good overview, but the combination I need is not in there.

I have attached an example of the basic thing i'm looking for.

Re. :Re: Ignore selection without Set Analysis

martin59 — Fri, 21 May 2010 15:26:29 GMT

I don't understand why you want to see QuotationID 1 in your example.

When you select 05/05/2010, you filter on QuotationID 3 which is associate. QuotationID 1 is associate to 05/05/2010 for the quotationDate and not the ProjectHourDate.

Ignore selection without Set Analysis

boorgura — Fri, 21 May 2010 15:27:22 GMT

So right now when nothing is selected - it shows id - 1 and date 5/5 amount - 5K

So what do u want to display when Reponsible/Project name is selected??

Ignore selection without Set Analysis

boorgura — Fri, 21 May 2010 15:31:40 GMT

I dint exactly get what you need.

But try this and let me know if this is what you are looking for?

SUM(all <QuotationID, QuotationName> IF(MONTH(QuotationDate) = MONTH(TODAY()),Amount))

replace ur current expression with the above one.

Let me know if it works!!!

Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 15:38:09 GMT

The chart is an overview of all quotations for the roling month. But normally in the bussiness process these are new quotations and 99% of the time their is nobody working on a new quotation because it's not officially closed as a deal.

On my other tabs I have overviews showing capacitiy, hour transactions, etc. In these sheets you need have a year selected at all times.

But by doing so this has effect on my quotations overview for the rolling month. It will only show new quotations where hours are made by employees. So that is why I need to ignore selections from my Projecthours table.

Ignore selection without Set Analysis

dpietersz — Fri, 21 May 2010 15:55:31 GMT

That's working in the example. But within my original QVW with a bit more complex data cloud it's not doing what I want.

But thx for your help guys. I've almost got it working thanks to you

Re. :Re: Re. :Re: Re. :Ignore selection without Set Analysis

bimartingo — Thu, 01 Mar 2012 19:52:17 GMT

Yes! Really good example. Simple to understand.

Helps and Reference Guides should be plenty of it.