Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Comparing fields in Set Analysis

Hi all,

Hope you can help, I'm struggiling to be able to find the right code to do a simple field - field sum.

Essentially I have a selection of Customers that I want to subtract their values from the Benchmarks we have set to identify if they are up/down vs that benchmark

Each Benchmark and Customer/Product has a Value in a table

  

Product NameThreeMonth
Customer172.21%
Customer260.00%
Customer365.84%
Customer467.58%
Customer565.43%
Customer666.21%
Customer763.26%
Benchmark166.03%
Benchmark2

65.24%

And in another table I state which Benchmark relates to which customer

  

Product NameBenchmark Name
Customer1Benchmark1
Customer2Benchmark1
Customer3Benchmark1
Customer4Benchmark2
Customer5Benchmark2
Customer6Benchmark2
Customer7Benchmark2
Benchmark1n/a
Benchmark2n/a

For Customer 1 it is benchmarked against Benchmark 1, so the sum should be 72.1 - 66.03, however I'm unable to write the correct code to have it identify that Benchmark 1 is related to Customer1 in this way,

Please help

Many thanks

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Comparing fields in Set Analysis

Would be good to see what you expect returned with your data set.

This is how I would model your data according to my previous suggested solution.

View solution in original post

15 Replies
Highlighted

Re: Comparing fields in Set Analysis

What is your desired output going to look like?

Highlighted
Not applicable

Re: Comparing fields in Set Analysis

Hi Sunny,

It will be shown as a % so 72.1 - 66.03 = 6.07%

Many thanks

Highlighted
MVP
MVP

Re: Comparing fields in Set Analysis

Maybe something like this if you have selected a single Product Name (Customer1)

=Only(ThreeMonth) - Only({<[Product Name] = p([Benchmark Name]) >} ThreeMonth)

Highlighted
Not applicable

Re: Comparing fields in Set Analysis

Hi Stefan,

Need to operate in a Table/Pivot with the Customer/Product Name showing and then the Measures alongside for each Product so no selections

Many thanks

Ryan

Highlighted

Re: Comparing fields in Set Analysis

May be this

RangeSum(ThreeMonth,

-Pick(Match([Benchmark Name], 'Benchmark1', 'Benchmark2'), Only(TOTAL {<[Product Name] = {'Benchmark1'}>} ThreeMonth), Only(TOTAL {<[Product Name] = {'Benchmark2'}>} ThreeMonth)))

Highlighted
MVP
MVP

Re: Comparing fields in Set Analysis

Try solving this in your data model:

Products:

LOAD [Product Name]

  //  [Benchmark Name]

FROM

(ooxml, embedded labels, table is Sheet1);

LinkTable:

LOAD [Product Name],

  [Product Name] as Product,

  'Product' as Type

  //  [Benchmark Name]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE [Benchmark Name] <> 'n/a';

LOAD [Product Name],

  [Benchmark Name] as Product,

  'Benchmark' as Type

  //  [Benchmark Name]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE [Benchmark Name] <> 'n/a';

Fact:

LOAD [Product Name] as Product,

    ThreeMonth

FROM

(ooxml, embedded labels, table is Sheet2);

Product Name Sum({<Type = {'Product'}>} ThreeMonth) - Sum({<Type = {'Benchmark'}>}ThreeMonth)
Customer16,2%
Customer2-6,0%
Customer3-0,2%
Customer42,3%
Customer50,2%
Customer61,0%
Customer7-2,0%
Highlighted
MVP
MVP

Re: Comparing fields in Set Analysis

And if you change the last LOAD to:

Fact:

CROSSTABLE (MeasureType, Measure)

LOAD [Product Name] as Product,

    ThreeMonth

FROM

(ooxml, embedded labels, table is Sheet2);

You can create a pivot with one expression, showing MeasureType as second dimension:

Product Name MeasureType ThreeMonth
Customer1 6,2%
Customer2 -6,0%
Customer3 -0,2%
Customer4 2,3%
Customer5 0,2%
Customer6 1,0%
Customer7 -2,0%
Highlighted

Re: Comparing fields in Set Analysis

Motivated by Stefan, putting down the expression based solution in case you don't want to make changes to script


ThreeMonth-Pick(Match([Benchmark Name], 'Benchmark1', 'Benchmark2'), Only(TOTAL {<[Product Name] = {'Benchmark1'}>} ThreeMonth), Only(TOTAL {<[Product Name] = {'Benchmark2'}>} ThreeMonth))

Capture.PNG

RangeSum(ThreeMonth,

-Pick(Match([Benchmark Name], 'Benchmark1', 'Benchmark2'), Only(TOTAL {<[Product Name] = {'Benchmark1'}>} ThreeMonth), Only(TOTAL {<[Product Name] = {'Benchmark2'}>} ThreeMonth)))

Capture.PNG

Highlighted

Re: Comparing fields in Set Analysis

Data:

LOAD [Product Name],

     ThreeMonth

FROM

[Example (1).xlsx]

(ooxml, embedded labels, table is Sheet2);

Left Join(Data)

LOAD [Product Name],

     [Benchmark Name]

FROM

[Example (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD Distinct [Product Name] as [Benchmark Name],

     ThreeMonth as ThreeMonthBenchMark

Resident Data;

Final:

NoConcatenate

LOAD *,

     num(ThreeMonth-ThreeMonthBenchMark,'#0.00%') as Sum

Resident Data;

DROP Table Data;

Capture.JPG