Cross Table: How would I get the Jan month end aggregate to show as Feb month start to use as the starting point of the Feb aggregate... and so on? I am sure it's a series of OVER, but I cannot get this to calculate properly.

[Michael Stulak]

[Gaia Paolini]

can you show what the desired result is?

[Michael Stulak]

Thank you Gaia - So the desired result mimics the table above. Each month we have + / - info (Calc's A - D) and would end the month with an aggregate number. Ex. Jan 2023 Start is 500... then the January 2023 month end would be 500-20+23-18+20 = 523.

Then February month would utilize the Jan2023 month end calculated total (523) as the Feb month Start to use for Feb aggregation: 523-3+16-18+25=561.

Then March month would utilize the Feb 2023 month end calculated total (561) as the March Month starting point...

...and on.

What I am needing & experiencing trouble with is the exact OVER formula to:

1) Carry over the previous month end to be the start of the next month

2) Use that carry-over from previous month to be used at the starting point for the next month aggregation. (ie, use Jan2023 month end total as starting point for Feb2023 aggretation)

Hopefully this helps.

[Gaia Paolini]

I kind of see what you want in principle, but what you show looks like a cross table, without mention of how you set the axes. Could you show the structure of the underlying data table, where I guess the calculations could be made?

[Michael Stulak]

Thank you @Gaia Paolini​ Attached is some sample data to illustrate what I'm trying to accomplish in SpotFire and what I have thus far in a Cross Table.

So below, I separated out the current month and future months (first two rows but this doesn't have to be the case --- i was in the midst of trial & error with the "FIRST" function since last years data is not included.

I would need to calculate Feb Headcount Forecast starting with the 318 and ending with 315.

315 would then be populated for March 2023 HC (row 2) starting point... and so on.

 

//Current Month HC

If((DatePart('mm',First([MonthYear])) & "-" & DatePart('yy',DateTimeNow()))=(DatePart('MM',DateTimeNow()) & "-" & DatePart('yy',DateTimeNow())),

sum(case when [DataType] in ("HeadcountActuals") then [HC] else null end)) as [Current Month HC], 

//HC Actuals

Sum(case when [DataType] in ("HeadcountActuals") then [HC] else null end)

OVER (Previous([Axis.Columns]))

- First(Count([Termination Date]) Over (AllPrevious([Axis.Columns]))) OVER (Previous([Axis.Columns]))

+ First(Count([Hire Date]) Over (AllPrevious([Axis.Columns]))) OVER (Previous([Axis.Columns]))

- Sum(SN([Monthly Attrition],0)) OVER (Previous([Axis.Columns]))

+ UniqueCount([Job ID]) OVER (Previous([Axis.Columns]))

as [HC], 

//Terminations

-Count([Termination Date]) as [Terminations], 

//New Hires Not Started Yet

Count([Hire Date]) as [New Hires], 

//Monthly Attrition Forecast

-SN(Sum([Monthly Attrition]),0) as [Monthly Attrition], 

//Jobs Forecast

UniqueCount([Job ID]) as [Jobs Forecast], 

//Headcount Forecast

Sum(case when [DataType] in ("HeadcountActuals") then [HC] else null end)

- Count([Termination Date]) Over (AllPrevious([Axis.Columns]))

+ Count([Hire Date]) Over (AllPrevious([Axis.Columns]))

- SN(Sum([Monthly Attrition]),0)

+ SN(UniqueCount([Job ID]),0) as [Headcount Forecast]

[Michael Stulak]

@Gaia Paolini​ do you happen to have any further ideas on this one? Thanks.

[Gaia Paolini]

I could not download the example as it seems to be a 49 page image. Please have a look at these tips:

https://community.spotfire.com/s/article/Quick-Tips-on-Asking-Community-Questions

Maybe somebody else in the community can help you.

[Heleen Snelting]

Hi Michael, I was able to download the Excel file - let's see if someone in the community has some suggestions on how to approach this. The excel file might be useful to test it out. I would look for a way to define the value for Month Start for month x. Make that 'lookup' or equal the value of the Month End total for the previous month (x-1) and display that. I think that is your question.

[Michael Stulak]

Thank you @Heleen Snelting​  --- glad you were able to download the excel file... I'm not sure why it comes through as an image attachment. you are correct in your assessment above. Ex. Feb month start (x) would be the aggregate of previous month (x-1) and so on throughout the year. Thank you - welcome any and all feedback / ideas from the Community. I have also created a downloadable link here for that sample data:

https://dl.dropboxusercontent.com/s/gdwco9t4u0038wl/sample%20data%20_1.xlsx?dl=0

[Andrew Berridge]

Hi @Michael Stulak​,

I think it's difficult to do with the data in the format that you've sent - from looking at it, it looks like you've attempted to pivot the data, but since you've also got lots of sparsely populated columns with the different types of data in it, it's difficult to figure out a reasonable aggregation that makes sense.

Could you experiment with the data transformations in Spotfire to get to an easier to understand sample data set? It could be a combination of calculation and Pivot transformations

I'm thinking of just having columns something like:

Date, DataType, HC

Where DataType would be HeadCount, Termination Count, Attrition rate, etc.

Then at least we could easily sum these up. It might also get rid of the large numbers of empty rows. In fact, that's the first thing I noticed when I opened the sample data - the empty rows just make the data that much more difficult to understand!

Can you do that? It would make it much easier for us to understand what you're trying to do.

Thanks,

Andrew (TIBCO Data Science)

[Michael Stulak]

Thank you @Andrew Berridge​ -- Yes, the original sample data is laid out in column format as my data table does have left outer joins (and other transformations) so everything is in column format. I have put the sample data into SF and did a pivot/unpivot to get to the following format per your request in the link below. I'm not sure if the volume of my operational dataset will be feasible get into this structure, as it has to be dynamic enough to update based on marking within over all visualization. Also, screen shot again of what I'm trying to accomplish here --- so for Jan total (318) it would be used as the starting point for Feb2023 Headcount and so on.

https://dl.dropboxusercontent.com/s/zdazks8x9xbtg6m/sample%20data%20_%20Sheet1.xlsx?dl=0

[Andrew Berridge]

Hi Michael - that's great - thank you! Indeed I can now see much more clearly how your data is structured and what you're trying to do with it.

I think that you should explore the option of calculating a cumulative sum and displaying it in the cross table. You can get an idea for how to write a cumulative sum expression by creating a visualisation (in my case I used a line chart) and choosing the Cumulative Sum aggregation. You can then copy and paste that expression into a calculated column and adjust it as necessary. It will be an OVER expression somewhat like this: Sum([New Value]) THEN Sum([Value]) OVER (AllPrevious([Axis.Rows]))

Except you'll need to use something different from [Axis.Rows] - you'll need to translate that into something that works with the MonthYear column.

Then you should be able to build on that and show the result of the cumulative sum in the cross table.

I hope this gives you further ideas as to how to proceed. Please let us know how you get on!

Andrew