Set Analisys - Matching fields

[shane_spencer]

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.

[YoussefBelloum]

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

)

[shane_spencer]

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.

[techvarun]

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)

[shane_spencer]

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

[shane_spencer]

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.

[shane_spencer]

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.

[pradosh_thakur]

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