Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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)', ', '))))
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
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.
Perfect, thank you so much for the assistance on this!