Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
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