Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

muhammadasfand
New Contributor

Confusion with using Sum function for different units

Hi,

i have the following expression that is causing some issues in Count.

Sum({<TransDate={'>=$(=MonthStart(today(),-12))<=$(=MonthEnd(today(),-1))'}>}$(UnitValue))

$(UnitValue) is a variable that either passes 1 or a value of TEUs e.g. if($(vUnits)=0,TEUs,1)

In case of passing TEUs the sum comes perfect whereas while passing 1, i just want the sum function to behav like count function and just gives a count of all rows but its giving a bigger count than it should be. My problem is i have to stick with Sum in my expression.

Any suggestions?

Thanks,

1 Solution

Accepted Solutions
ogautier62
Valued Contributor II

Re: Confusion with using Sum function for different units

Hi,


with 1 :

it will count all rows of your database I think because you have no field

with that :

if($(vUnits)=0,TEUs,TEUs-TEUs+1)

it will count all not null rows of TEUs

(for the fun)

prefer Sunny's solution it's clean


4 Replies

Re: Confusion with using Sum function for different units

May be you can do this

If($(vUnits) = 0,

Sum({<TransDate={'>=$(=MonthStart(today(),-12))<=$(=MonthEnd(today(),-1))'}>} TEUs),

Count({<TransDate={'>=$(=MonthStart(today(),-12))<=$(=MonthEnd(today(),-1))'}>} 1))

ogautier62
Valued Contributor II

Re: Confusion with using Sum function for different units

Hi,


with 1 :

it will count all rows of your database I think because you have no field

with that :

if($(vUnits)=0,TEUs,TEUs-TEUs+1)

it will count all not null rows of TEUs

(for the fun)

prefer Sunny's solution it's clean


muhammadasfand
New Contributor

Re: Confusion with using Sum function for different units

You are right about it and i have already considered it the last solution. Ideally i want to do it using Sum() as i have so much conditional expressions and i don't want to do it that way it will increase a lot of code.

muhammadasfand
New Contributor

Re: Confusion with using Sum function for different units

That looks cool and seem to have worked.

Community Browser