Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pelle90
New Contributor II

Using filter to create 2 new measures from one measure.

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 /

Labels (2)
2 Solutions

Accepted Solutions
MVP
MVP

Re: Using filter to create 2 new measures from one measure.

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pelle90
New Contributor II

Re: Using filter to create 2 new measures from one measure.

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

7 Replies

Re: Using filter to create 2 new measures from one measure.

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
New Contributor II

Re: Using filter to create 2 new measures from one measure.

-

 

 

MVP
MVP

Re: Using filter to create 2 new measures from one measure.

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pelle90
New Contributor II

Re: Using filter to create 2 new measures from one measure.

-

pelle90
New Contributor II

Re: Using filter to create 2 new measures from one measure.

-

pelle90
New Contributor II

Re: Using filter to create 2 new measures from one measure.

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
New Contributor II

Re: Using filter to create 2 new measures from one measure.

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