Ignore selection without Set Analysis

[dpietersz]

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?

[martin59]

Hello,

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

sum(ALL ...)

or

sum(TOTAL ...)

Martin

[dpietersz]

Yes I tried those. No luck with that

[boorgura]

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)

[dpietersz]

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

[martin59]

f1 & f2 are dimensions of your chart.

This syntax only works in a chart.

[boorgura]

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.

[dpietersz]

Ok,

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

[boorgura]

I think I got it wrong now.

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

[dpietersz]

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.