Comparing fields in Set Analysis

[#1]

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

[#2]

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.

[#3]

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

[sunny_talwar]

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

or

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

[#5]

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

[swuehl · Accepted solution]

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.

[#7]

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