topic Apples to Apples Comparison in QlikView

Apples to Apples Comparison

brooksc57 — Sat, 06 Aug 2016 01:07:06 GMT

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

thx

Brooks

Re: Apples to Apples Comparison

MindaugasBacius — Sat, 06 Aug 2016 09:30:25 GMT

Sum({$<
  transtype={'returns'}
  ,company-={'xyz corp'}
>}
dlr)
/
Sum({$<
  transtype={'sales'}
  ,company-={'xyz corp'}
>}
dlr)

If you use above expression you will get this:

Re: Apples to Apples Comparison

Kushal_Chawda — Sat, 06 Aug 2016 09:50:33 GMT

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)

Re: Apples to Apples Comparison

brooksc57 — Mon, 08 Aug 2016 03:33:10 GMT

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!!!

Re: Apples to Apples Comparison

brooksc57 — Mon, 08 Aug 2016 03:36:39 GMT

thanks kindly for your response Mindaugas!