topic Generate multiple calculated dimensions based on loop in App Development

Generate multiple calculated dimensions based on loop

benwashburne — Sat, 16 Nov 2024 01:22:08 GMT

Hi Sunny/Stefan,

I was hoping one of you could help me with this one. I have a table with the following fields:

opp_id, pop_start_date, pop_end_date, expected_revenue

The goal is to generate one calculated dimension for each month. The result is a number. I have successfully created the logic for one month, but I am having difficulty in converting to some sort of a loop so I don't have to have this logic pasted 10s of times for all the possible months. I know that I need to utilize some MIN and MAX variables to establish the date range and then loop those variables through this logic. But how do I dynamically generate each dimension so that it too increases by 1 month each time?

[Period_of_Performance_calculate]:
Load *,

IF('9/1/2018'<= (PoP_Start__c - day(PoP_Start__c)) or '9/1/2018'>monthend(PoP_End__c,0),null(),
RangeMax(rangemin(addmonths('9/1/2018',1)-PoP_Start__c, addmonths('9/1/2018',1)-'9/1/2018', PoP_End__c-'9/1/2018'+1,PoP_End__c-PoP_Start__c+1),0)
*[Expected_Revenue__c]
/(PoP_End__c-PoP_Start__c+1)) as [Sep-18] //this would increment each time so the next dimension would be [Oct-18] then [Nov-18] all the way until the MAX(pop_end_date).

Resident [Period_of_Performance__c];
Drop Table [Period_of_Performance__c];
Rename table [Period_of_Performance_calculate] to [Period_of_Performance__c];

Re: Generate multiple calculated dimensions based on loop

sunny_talwar — Mon, 28 Sep 2020 14:46:48 GMT

Not entirely sure I understand, would you be able to elaborate a little more.

Re: Generate multiple calculated dimensions based on loop

benwashburne — Mon, 28 Sep 2020 15:18:12 GMT

@sunny_talwar Sure thing.

I've attached an export of what I am trying to create. Imagine there are $$ values in the expected_revenue column. The column labelled 'Sep-18' stands for September-2018. The values in this column are derived from the formula below (they show the amount of revenue you'd expect to collect in that month). I need to create this Month Year field for all the months between the PoP_Start and PoP End dates. As state, the value in this Month Year field is derived from the formula below. However, it is a static formula. Therefore, I would need to copy, paste, and update the date fields for each month within the range (9/1/2018, 10/1/2018, 11/1/2018, etc etc) in order to create each of the Year Month Dimensions needed. I need to find a smarter, more optimized way to do this. I assume it involves generating a variable list for all the possible months in range and then looping the statement below, I just have no idea where to begin.