Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
josecanalla
New Contributor III

Divide values with column null

I have a table with the expenses of truck drivers. One of the columns of this table, is the "driver_id".

Sometimes, the "driver_id" is null because of the expense is not imputable to any driver. I want to get all of the expenses with driver_id null and divide that value into all of the drivers. How can I do it in Qlik Sense?

For example:

$500 of expenses with driver_id null. Then if I have 5 drivers, I want to put $500/5 = $100 to each driver and show it, for example, in a pivot table.

3 Replies
devarasu07
Honored Contributor II

Re: Divide values with column null

hi,

Try like this

Your dimension

=if(len(driver_id)>0,driver_id)

note: remember to suppress the null value

measure:

sum( total{$<driver_id ={"=len(driver_id)<=0"}>}expenses)/count(total  distinct {$<driver_id ={"=len(driver_id)>0"}>} driver_id)

Capture.JPG

Capture.JPG

prasadmundewadi
New Contributor III

Re: Divide values with column null

Try this

Sum(if(IsNull(driver_id),Expense))/Count(distinct (driver_id))

josecanalla
New Contributor III

Re: Divide values with column null

It works and with this I can get the total of expenses with driver_id null.

But now how can I sum this value in a pivot table with "driver_id" and "product_id" as rows, "monthyear" as column, and "total" (which is a sum) as measure?