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]

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 ?

[boorgura]

Hey Martin,

That's a good example.

Thanks man.

[dpietersz]

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

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

[martin59]

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.

[boorgura]

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

[boorgura · Accepted solution]

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

[dpietersz]

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.

[dpietersz]

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

[bimartingo]

Yes! Really good example. Simple to understand.

Helps and Reference Guides should be plenty of it.