Forum Discussion
Solved
help needed to build multi dimensional formula
Hi everyone,
I need help building a logic in excel, here’s what I’m trying to do:
I have two tabs in excel, sheet 1 and the other is sales tab,
In sheet 1, I have following data points: The dates are in form of MMDDYYYY
Now, I want the logic to always pull sales based on tracking start and end dates for DSeA first, second SCC, and lastly the SV engagement.
So, it should start pulling the sales for DSeA for month starting from February to October
Then for SCC it should just pull sales for Nov to Dec since there is an overlap in time periods, I don’t want the logic to double count sales for both the engagement type.
Lastly, for SV it should just pull sales for Jan 2023 as there is overlap in time periods for this engagement type as well.
Furthermore, the next check is the platform, once, I have TR pulled, then it should pull the other platforms JR and CM
The hierarchy will always be DSeA first based on start and end date next SCC and at last the SV engagement
Sales Tab:
AKuma0411 I'm not sure how well this will perform with a larger dataset, but you can give it a try anyways...
=LET( ucn, A2:A16, type, C2:C16, platform, E2:E16, start, F2:F16, end, G2:G16, months, Sales!E1:P1, data, Sales!E2:P10, ucns, Sales!A2:A10, platforms, Sales!C2:C10, MAP(ucn, type, platform, start, end, LAMBDA(u,t,p,s,e, LET( incl1, (months>=s)*(months<=e), incl2, IF(t<>"DSeA", NOT((months>=MINIFS(start, ucn, u, type, "DSeA", platform, p))*(months<=MAXIFS(end, ucn, u, type, "DSeA", platform, p)))*incl1, incl1), incl3, IF(t="SV", NOT((months>=MINIFS(start, ucn, u, type, "SCC", platform, p))*(months<=MAXIFS(end, ucn, u, type, "SCC", platform, p)))*incl2, incl2), IFERROR(SUM(FILTER(FILTER(data, (ucns=u)*(platforms=p)), incl3, 0)), 0)))) )MAP Function
If there's too much of a lag when applied to a larger dataset, try the second workbook (see attached), which generates a report based on the selected range of UCN's (but keep the range to a reasonable number... if too many are selected, it will also perform poorly).
Note: these will only work with Excel for MS365 or Excel for the Web.
5 Replies
AKuma0411 I'm not sure how well this will perform with a larger dataset, but you can give it a try anyways...
=LET( ucn, A2:A16, type, C2:C16, platform, E2:E16, start, F2:F16, end, G2:G16, months, Sales!E1:P1, data, Sales!E2:P10, ucns, Sales!A2:A10, platforms, Sales!C2:C10, MAP(ucn, type, platform, start, end, LAMBDA(u,t,p,s,e, LET( incl1, (months>=s)*(months<=e), incl2, IF(t<>"DSeA", NOT((months>=MINIFS(start, ucn, u, type, "DSeA", platform, p))*(months<=MAXIFS(end, ucn, u, type, "DSeA", platform, p)))*incl1, incl1), incl3, IF(t="SV", NOT((months>=MINIFS(start, ucn, u, type, "SCC", platform, p))*(months<=MAXIFS(end, ucn, u, type, "SCC", platform, p)))*incl2, incl2), IFERROR(SUM(FILTER(FILTER(data, (ucns=u)*(platforms=p)), incl3, 0)), 0)))) )MAP Function
If there's too much of a lag when applied to a larger dataset, try the second workbook (see attached), which generates a report based on the selected range of UCN's (but keep the range to a reasonable number... if too many are selected, it will also perform poorly).
Note: these will only work with Excel for MS365 or Excel for the Web.
djclements Thank you for your response! In the first method, for the line item 8 it should pull sales for first 6 months and not Jan and feb sales, I there a easy way to tweak this logic ?
Sheet 1:
Sales Tab:
Thanks again!
AKuma0411 I have to say, I believe that would contradict your hierarchy logic... you said "DSeA" first, "SCC" second and "SV" third. In line 7, "SCC" for platform "MS" (March to December) takes precedence, does it not? Because of the overlap, "SV" only gets January and February. At least that was my understanding... let me know.
