Spreading costs

[timpoismans]

Hi all

I've stumbled on an issue when trying to spread costs.

My data is as follows:

And it's structured like:

CustomerNr - AccountNr (as in Great Ledger Account) - Account Name - CustomerType - Amount

Each customer belongs to an Office and is listed under the office by his/her CVATNO. 
Each office has a certain amount of costs booked to it. Now we want to spread those costs on certain customers.

I know which customers I need to spread the costs too, I know how to spread it, but I'm not able to get the value I want to spread to those customers.

So, what I want to achieve is the following:

The costs that we want to spread have been booked to a virtual customer with code 9999. Now the costs of customer 9999 need to be spread to the other customers, within that office. My table looks like this:

The measure I'm currently using is:

Sum(total<CVATNO,Klantennummer>
	{<Rekeningnaam={'*Euroworker*'},
	Rekening={'6*'}-{'620250','620260','620270','620300','620310','620320'},
    Klantennummer={9999},
    EuroKostVerdelen={1}>}
    Bedrag)

but this fails to spread to the other CVATNO's.

What I want to achieve is the following:

The green-squared values are what I want to achieve: the costs of customer 9999, called [Algemeen Euroworkers] spread to all the other customers listed by their CVATNO.

If anything isn't clear, please ask.

Any help is appreciated!

Regards,

Tim P.

[jensmunnichs]

Hey,

So this is going to be a bit of a guess, but I think the only reason why the result would be different for a different dimension is because, well, it's a different dimension. That's what the 'total' is there for in your expression obviously, but I'm thinking that maybe because you're only including CVATNO and Klantennummer in the total, you're not getting any results.

For example, if CVATNO 0810633651 has no account names containing 'Euroworker', no account numbers starting with 6, and/or no EuroKostVerdelen = 1, it won't return any results*. If that's the case, just add those fields to the total list, or remove both and just have total in your expression like below.

Sum(total<CVATNO,Klantennummer>
{<Rekeningnaam={'*Euroworker*'},
Rekening={'6*'}-{'620250','620260','620270','620300','620310','620320'},
Klantennummer={9999},
EuroKostVerdelen={1}>}
Bedrag)

Again not 100% sure this is how it works, but it's worth a shot!

*EDIT: Made a mistake here, only dimensions in your table matter, not necessarily all parameters in your set analysis. From the pictures it seems like Account name is the only dimension missing from the total list, unless there are other dimensions underneath.

[timpoismans]

Hi

Thanks for the fast reply.

If I remove the [CVATNO] from the total qualifier, the cost spreads to the other CVATNO's. But it shows the total (of all the offices) of the costs that need to be spread, and not the total of the costs per office that need to be spread.

Each customer (and thus CVATNO) get a [CostSpreadKey] per office, as some customers are listed in more than one office.

[jensmunnichs]

I'm sorry, I think I may have misunderstood how the total <fields> modifier works. Judging by this post, you should be able to get what you want by doing this:

Sum(total<Kantoor>
	{<Rekeningnaam={'*Euroworker*'},
	Rekening={'6*'}-{'620250','620260','620270','620300','620310','620320'},
    Klantennummer={9999},
    EuroKostVerdelen={1}>}
    Bedrag)

 Not sure what the exact name of your office name field is, but you get the point. Hope this works 🙂

EDIT: The part where I misunderstood was that I thought the fields after the total modifier were the ones being INcluded in the total modifier, while actually those fields are the ones being EXcluded.

[timpoismans]

Kantoor is the name of my office field 😉

But sadly it gives the same result: it returns the total of costs to be spread of all the offices, and not per office =/