topic Re: Set Analisys - Matching fields in QlikView

Set Analisys - Matching fields

shane_spencer — Fri, 06 Oct 2017 15:05:55 GMT

I've got a table that shows reconcilliations between two data sets - see attached

i.e. Detailed Breakdown

I want to make another table very similar that only shows the values where there's a difference.

i.e. Disparitas

Unfortunately if there is not a a value in either VAT Return (AMT_NET_TAXREP_src_1) or Intrastat (GOODS_INVOICE_VALUE) the value is not shown:

i.e.

sum({<iLINK={'=round(sum(GOODS_INVOICE_VALUE),0.01)<>round(sum(AMT_NET_TAXREP_src_1),0.01)'}>} GOODS_INVOICE_VALUE)

So the question is how can I show in the chart on the Disparitas page all the rows where the difference is great that zero? As per the example in the spreadsheet.

Re: Set Analisys - Matching fields

YoussefBelloum — Fri, 06 Oct 2017 15:39:07 GMT

you can try this on all the expressions of your table:

if(

(sum({<iLINK={'=round(sum(AMT_NET_TAXREP_src_1),0.01)<>round(sum(GOODS_INVOICE_VALUE),0.01)'}>} AMT_NET_TAXREP_src_1)

sum({<iLINK={'=round(sum(GOODS_INVOICE_VALUE),0.01)<>round(sum(AMT_NET_TAXREP_src_1),0.01)'}>} GOODS_INVOICE_VALUE)) >0,

YOUR_MEASURE

)

Re: Set Analisys - Matching fields

shane_spencer — Mon, 09 Oct 2017 08:08:59 GMT

Hi Youssef, thanks for the suggestion but this seemed to provide even fewer results.

Does anyone else have any suggestions? I've updated the attached qvw to a slightly updated version but it's essentially the same. I'm wanting to show on Disparitas tab all the values that were red on the Detailed Breakdown tab.

Re: Set Analisys - Matching fields

techvarun — Mon, 09 Oct 2017 09:12:08 GMT

Try the below expression

sum({<iLINK={'=round(sum(GOODS_INVOICE_VALUE),0.01)<>round(sum(AMT_NET_TAXREP_src_1),0.01)'}>} GOODS_INVOICE_VALUE) * Count(DISTINCT TAX_CODE_src_1&TAX_CODE_EU_INDICATOR_src_1&PARTY_SECONDARY_NAME_src_1)

Re: Set Analisys - Matching fields

shane_spencer — Mon, 09 Oct 2017 09:24:10 GMT

Nope. That just gives me the same as I had.

Re: Set Analisys - Matching fields

shane_spencer — Mon, 09 Oct 2017 09:29:20 GMT

I can easily create a table of the matching values using set analysis (see attached)

i.e.

{<LINK={'=round(sum(AMT_NET_TAXREP_src_1),0.01)=round(sum(GOODS_INVOICE_VALUE),0.01)'}>}

but as soon as I replace the = with <> I lose the values where one of the fields is empty (i.e. there's no matching value in the LINK)

I'm wondering if the answer is something to do with using E (Excluded). But not sure how that would work.

Re: Set Analisys - Matching fields

shane_spencer — Mon, 09 Oct 2017 09:48:24 GMT

I think it's possible that I was just being stupid and the formula works but I was narrowing the results because I'd got iCOUNTRY or gCOUNTRY selected.

Re: Set Analisys - Matching fields

pradosh_thakur — Mon, 09 Oct 2017 10:19:45 GMT

try this

sum({<iLINK={'=round(sum(GOODS_INVOICE_VALUE),0.01)~round(sum(AMT_NET_TAXREP_src_1),0.01)'}>} GOODS_INVOICE_VALUE)

Replaced <> with ~.

regards

Pradosh