Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
benwashburne
Partner - Creator
Partner - Creator

Generate multiple calculated dimensions based on loop

@sunny_talwar @swuehl 

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];

Labels (3)
2 Replies
sunny_talwar

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

benwashburne
Partner - Creator
Partner - Creator
Author

@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.  

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