Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!!!
Hello,
Have you tried with the 'ALL' or 'TOTAL' qualifiers ? Like that :
orsum(ALL ...)
sum(TOTAL ...)
Martin
Yes I tried those. No luck with that
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)
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
f1 & f2 are dimensions of your chart.
This syntax only works in a chart.
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.
Ok,
Then I can not use the solution. The fields I need to ignore are listboxes not in my chart.
I think I got it wrong now.
So, should a selection on the list box effect the calculations?
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.