topic Re: Comparing fields in Set Analysis in QlikView

Comparing fields in Set Analysis

Fri, 12 May 2017 11:15:43 GMT

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 Name	ThreeMonth
Customer1	72.21%
Customer2	60.00%
Customer3	65.84%
Customer4	67.58%
Customer5	65.43%
Customer6	66.21%
Customer7	63.26%
Benchmark1	66.03%
Benchmark2	65.24%

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

Product Name	Benchmark Name
Customer1	Benchmark1
Customer2	Benchmark1
Customer3	Benchmark1
Customer4	Benchmark2
Customer5	Benchmark2
Customer6	Benchmark2
Customer7	Benchmark2
Benchmark1	n/a
Benchmark2	n/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

Re: Comparing fields in Set Analysis

sunny_talwar — Fri, 12 May 2017 11:40:45 GMT

What is your desired output going to look like?

Re: Comparing fields in Set Analysis

Fri, 12 May 2017 11:45:34 GMT

Hi Sunny,

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

Many thanks

Re: Comparing fields in Set Analysis

swuehl — Fri, 12 May 2017 12:03:08 GMT

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

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

Re: Comparing fields in Set Analysis

Fri, 12 May 2017 12:04:22 GMT

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

Re: Comparing fields in Set Analysis

sunny_talwar — Fri, 12 May 2017 12:52:19 GMT

May be this

RangeSum(ThreeMonth,

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

Re: Comparing fields in Set Analysis

swuehl — Fri, 12 May 2017 13:41:52 GMT

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)
Customer1	6,2%
Customer2	-6,0%
Customer3	-0,2%
Customer4	2,3%
Customer5	0,2%
Customer6	1,0%
Customer7	-2,0%

Re: Comparing fields in Set Analysis

swuehl — Fri, 12 May 2017 13:45:52 GMT

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%

Re: Comparing fields in Set Analysis

sunny_talwar — Fri, 12 May 2017 13:46:37 GMT

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))

RangeSum(ThreeMonth,

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

Re: Comparing fields in Set Analysis

Kushal_Chawda — Fri, 12 May 2017 13:49:49 GMT

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;

Re: Comparing fields in Set Analysis

Fri, 12 May 2017 14:19:48 GMT

Hi Sunny,

That works in principle so thank you, however the overall dataset has around 300k rows, about 6k Customers and 2k Benchmarks so doing that for that volume isn't practical,

I've used yours and altered it slightly using a Concat function to auto create the list

RangeSum(ThreeMonth,

-Pick(Match([Benchmark Name], $(=chr(39) & concat(distinct [Benchmark Name], chr(39) & ',' & chr(39)) & chr(39))), Only(TOTAL {<[Product Name] = {'Benchmark1'}>} ThreeMonth), Only(TOTAL {<[Product Name] = {'Benchmark2'}>} ThreeMonth)))

However I need to then also make the last part relevent to an increasing list of variable numbers, I could in theory remake that entire last section using Concat though and some variables.

Re: Comparing fields in Set Analysis

Fri, 12 May 2017 14:21:09 GMT

Hi Stefan,

Thanks for this, I'll work on hard baking this into the data set at the load script level and confirm how I get on,

Many thanks

Ryan

Re: Comparing fields in Set Analysis

sunny_talwar — Fri, 12 May 2017 15:40:11 GMT

Here is the version with dollar sign expansion... again it might not be a bad idea to do it in script.... but just want to throw out all possibilities

ThreeMonth-Pick(Match([Benchmark Name], $(=chr(39) & concat(distinct [Benchmark Name], chr(39) & ',' & chr(39)) & chr(39))), $(=Concat({<[Product Name] = {'Benchmark*'}>}DISTINCT 'Only(TOTAL {<[Product Name] = {' & Chr(39) & [Product Name] & Chr(39) & '}>} ThreeMonth)', ', ')))

RangeSum(ThreeMonth,

-Pick(Match([Benchmark Name], $(=chr(39) & concat(distinct [Benchmark Name], chr(39) & ',' & chr(39)) & chr(39))), $(=Concat({<[Product Name] = {'Benchmark*'}>}DISTINCT 'Only(TOTAL {<[Product Name] = {' & Chr(39) & [Product Name] & Chr(39) & '}>} ThreeMonth)', ', '))))

Re: Comparing fields in Set Analysis

Mon, 15 May 2017 15:10:19 GMT

Hi Sunny, Stefan, Kushal,

Many thanks for the suggestions, they have all worked on the test set of data and I thought that would be enough for me to switch across to the live data however potentially where I simplyfied the test data drastically was probably not ideal, as the "ThreeMonth" in the live data isn't actually a field but a calculated dimension front end that looks like

exp(sum({$<[Date.autoCalendar.MonthsAgo] = {"<=$(=Min([Date.autoCalendar.MonthsAgo] + 2))"}>}log(returncalc))) -1

I've attached a datafile that is a near exact match for what I'm working with, (with the content names changed).

Ultimately is it still possible to do what you've done to this data set? I've struggled with switching it over

Many thanks

Ryan

Re: Comparing fields in Set Analysis

swuehl — Mon, 15 May 2017 18:40:22 GMT

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.

Re: Comparing fields in Set Analysis

Tue, 16 May 2017 10:38:36 GMT

Perfect, thank you so much for the assistance on this!