Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shwetagupta
Contributor

Sum

Hi All,

I am having a field named status which has three codes i.e SL, SR, and SRDMG. which represents my sales nature.


and I am having field of Qty

whenever my code is SL , i need to subtract sales coresponding to SL and SR

i.e if Status = SL then my sales = sum of Qty under SL- sum of Qty under SR

then if Status = SR then sales = sum of Qty under SR

also, if Status=SRDMG then Sales = sum of Qty under SRDMG

so whenever status is SL in filter i need above desired result corresponding to branch name

Can someone please guide me to write an expression.

Data look like this

Branch           qty          Status(in filter)

A                    10          SL

A                    2            SR

B                     7             SR 

C                     3             SRDMG

Result is when I see my table drawn on Net Sales

Branch          Net Sales

A                    8

B                    7

C                    3

10 Replies
MVP
MVP

Re: Sum

May be like this:

Dimension:

Branch

Expression:

If(Count({<Status = {'SL'}>} Status) > 0,

RangeSum(Sum({<Status = {'SL'}>} qty), -Sum({<Status = {'SR'}>} qty)),

Sum(qty))

MVP
MVP

Re: Sum

Sample attached

Capture.PNG

shwetagupta
Contributor

Re: Sum

Hi Sunny,

Thanks a lot for your reply but it didn't worked.

but I have tried and got this as my solution

If (Status= 'SL', ( Sum( {$<Status={'SL'}>} [qty] )- Sum( {$<Status={'SR'}>} [qty] )), sum(qty))

MVP
MVP

Re: Sum

Was Status one of your dimension? If not, then I am amazed this worked.

shwetagupta
Contributor

Re: Sum

Yeah it is one of my dimension.

MVP
MVP

Re: Sum

I still have my concerns, but if it worked for you, then I am not going to push my concerns here

Best,

Sunny

shwetagupta
Contributor

Re: Sum

No, please go ahead and let me know.

though I am getting results but may be there can be a aspect which I haven't considered.

MVP
MVP

Re: Sum

When your dimension Status has Value SL, then Sum({$<Status={'SR'}>} [qty]) would always be 0. So, in essence, this two should be same

If (Status= 'SL', ( Sum( {$<Status={'SL'}>} [qty] )- Sum( {$<Status={'SR'}>} [qty] )), sum(qty))


and


If (Status= 'SL', Sum({$<Status={'SL'}>} [qty]), sum(qty))


and


sum(qty)

Unless you use Total (with or without field qualifier)

If (Status= 'SL', (Sum({$<Status={'SL'}>} [qty]) - Sum(TOTAL <FieldName> {$<Status={'SR'}>} [qty])), sum(qty))

I guess the way to check this would be to look for a case where you have SL and SR and is the calculation done correctly?

shwetagupta
Contributor

Re: Sum

Thanks for your valuable inputs.

I ll check once again from my database. Though I do now think the need of review to my formula.

Thanks again