Dynamic Column Headers Formula Issue

[ethanschuttler]

Hi All,

Apologies for the ambiguous title but I'm not sure what to call this.

I am having an issue figuring out how to create formulas for dynamic column headings. We have a DB table that updates column headings daily. So today the first column is 3-1-19 and the following columns are the previous column +7 days for a full 52 weeks. 

Why this is an issue is because I need to count values for the next 20 weeks. And if I select the specific column of 3-1-19 it will be gone on Monday and replaced with 3-4-19. 

I am not even sure how to start this, but basically I need something that will allow me to count values < 0 where the column header is a date < DATEADD(D, -1, DATEADD(M, DATEDIFF(M, 0, GETDATE()) + 5, 0))

Any ideas would really help me out.

Thanks!

Ethan

[MarqueeCrew · Accepted solution]

@ethanschuttler,

Here is a solution to your challenge.  First, I assume that there is a KEY to your record.  If not, you can assign a record ID to each incoming record.

1. TRANSPOSE the data and choose the KEY FIELD(S) as the key fields and select the known dates as the data fields.  You need to keep the DYNAMIC or UNKNOWN Fields checked.
2. FILTER the data to select the right columns and values with
   

   DateTimeParse([Name],"%m-%d-%y") <=
   DateTimeAdd(DateTimeStart(),140,"Days")
   AND 
   [Value] < 0

   This formula will keep the Next 20 weeks worth of Negative values.
3. SUMMARIZE the data with a GROUP BY the KEY and count of the KEY.  This will give you a count of all of the upcoming negative values.

Cheers,

Mark

[ethanschuttler]

That worked! Thank you!