topic Re: Intercompany Sales Variance table in QlikView

Intercompany Sales Variance table

martyn_birzys — Fri, 13 Mar 2015 17:28:49 GMT

I need to build a report on intercompany sales - a bit similar to contras, but not quite.

I have 3 companies tables and sales amount fields

Company A

Sales to B

Sales to C

Company B

Sales to A

Sales to C

Company C

Sales to A

Sales to B

To display sales matrix I have no problem:

But how to build a table to show variance in sales between companies? e.g. Company A sales to B vs Company B sales to A.

I can do it with a bunch of text boxes and this script:

Sum({< CompanyName={'Company A'}>}[Sales to B]) - Sum({< CompanyName={'Company B'}>}[Sales to A])

But how to build this into a table?

MK_QSL — Fri, 13 Mar 2015 17:32:00 GMT

You can do it by Script end... Provide some sample data to work..

martyn_birzys — Tue, 21 Apr 2015 08:59:55 GMT

I would have 6 text boxes with the following expressions:

Sum({< CompanyName={'Company A'}>}[Sales to B]) - Sum({< CompanyName={'Company B'}>}[Sales to A])

Sum({< CompanyName={'Company A'}>}[Sales to C]) - Sum({< CompanyName={'Company C'}>}[Sales to A])

Sum({< CompanyName={'Company B'}>}[Sales to A]) - Sum({< CompanyName={'Company A'}>}[Sales to B])

Sum({< CompanyName={'Company B'}>}[Sales to C]) - Sum({< CompanyName={'Company C'}>}[Sales to B])

Sum({< CompanyName={'Company C'}>}[Sales to A]) - Sum({< CompanyName={'Company A'}>}[Sales to C])

Sum({< CompanyName={'Company C'}>}[Sales to B]) - Sum({< CompanyName={'Company B'}>}[Sales to C])

In reality I have 8 companies, so I end up with sheet-full of text boxes which are time consuming to maintain.

I need to build a table where instead of Sales values I would have Variance values.