Using filter to create 2 new measures from one measure.

[pelle90]

Hi. I have a very basic problem. I want to create 2 new measures based on criterias for an existing measure.

Lets say I have the measure revenue.

And I want to create a new customized measure I call for example "inactive customer", criterion: revenue=0

The other measure I want is then "active customer" = revenue >0. 

I was trying to create a original object like: Count(revenue>0) and name "number of active customer" but realized it is not giving the right numbers.

Do I have to create a script (in that case what does it look like) or what is the most suitable solution?

Thanks in advance /

[walxiney_Planner]

Try use for "Inactive Costumer" Count({<Revenue = 0 >} Revenue) and for the "Active Customer" Count({<Revenue > 0 >} Revenue).

Or you can do it on the script, make a LOAD of data using the WHERE Revenue = 0 and after this you can concatenade the table with your original table and make the same for the Revenue  > 0

Something like this...

Table1:

Load

Customer as "Inactive Costumer"

Where

Revenue  = 0

Table2:

Load

Customer as "Active Costumer"

Where

Revenue  > 0

[pelle90]

-

[jonathandienst · Accepted solution]

This sort of analysis is simpler if you create some flags in the load script and use these in your expressions. For example:

LOAD ...
	Customer,
	Revenue,
	If(Revenue > 0, 1, 0) as ActiveFlag,
	...
	DueDate,
	ShipmentDate,
	If(ShipmentDate > DueDate, 1, 0) as LateFlag,
	...

Then your expressions are:

Active:    Count({<ActiveFlag = {1}>} distinct Customer)
Inactive:  Count({<ActiveFlag = {0}>} distinct Customer)

And so the same with the LateFlag.

[pelle90]

-

[pelle90]

-

[pelle90]

Found the most likely reason. SalesorderdetailID is for every item in the order (and salesorderId for every order). So if I have ordered 3 pieces in one order the flag will be counted 3 times despit that it probably only should be counted once.

The question is then, how do I change so it only calculates late-flag once per each SalesOrderID.

[pelle90 · Accepted solution]

And I now found the solution on my own. Nice feeling!