Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Generate multiple calculated dimensions based on l...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

benwashburne

Partner - Creator

2020-09-28
09:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Generate multiple calculated dimensions based on loop

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

612 Views

2 Replies

sunny_talwar

MVP

2020-09-28
10:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

593 Views

benwashburne

Partner - Creator

2020-09-28
11:01 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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

580 Views

Community Browser