Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

brooksc57
New Contributor III

Apples to Apples Comparison

Hello QV Gang,

Looking for some assistance in getting a desired result.

I still want to show the xyz corp, but don't want to include it in the % expression.

as illustrated below it shows a 5% total but I want it to show 9.2%... since there are no returns for xyz corp I don't want those sales values used in the calculation which would bring it to a 9.2%.

The terminology I've always used is calling this an apples to apples comparison.

I'd rather not filter out the "bad apples" but show them in the report/chart as well...so those 'holes' can be fixed.

Also attached is this sample .qvw

Capture.PNG

thx

Brooks

1 Solution

Accepted Solutions

Re: Apples to Apples Comparison

I am considering that calculation should be dynamic, so let's say if you have return 0 for abc corp then you should exclude abc corp and vice versa. In this case above expression will not work as company name is hard coded. You can use below expression in that case

=Sum({$<transtype={'returns'}, company={"=Sum({$<transtype={'returns'}>}dlr)>0"}>}dlr)/

Sum({$<transtype={'sales'}, company={"=Sum({$<transtype={'returns'}>}dlr)>0"}>}dlr)

4 Replies
MindaugasBacius
Valued Contributor II

Re: Apples to Apples Comparison

Sum({$<

  transtype={'returns'}

  ,company-={'xyz corp'}

>}

dlr)

/

Sum({$<

  transtype={'sales'}

  ,company-={'xyz corp'}

>}

dlr)

If you use above expression you will get this:

Screenshot_1.jpg

Re: Apples to Apples Comparison

I am considering that calculation should be dynamic, so let's say if you have return 0 for abc corp then you should exclude abc corp and vice versa. In this case above expression will not work as company name is hard coded. You can use below expression in that case

=Sum({$<transtype={'returns'}, company={"=Sum({$<transtype={'returns'}>}dlr)>0"}>}dlr)/

Sum({$<transtype={'sales'}, company={"=Sum({$<transtype={'returns'}>}dlr)>0"}>}dlr)

brooksc57
New Contributor III

Re: Apples to Apples Comparison

Thanks kushal...I was getting close to this method at first blush, but I wasn't including the double quotes so I was having 'error in expression'...your solution is perfect.  didn't see this type of post anywhere on qlik comm so I hope this question and your response will help lots of folks!!!

brooksc57
New Contributor III

Re: Apples to Apples Comparison

thanks kindly for your response Mindaugas!