Dynamic Summarize the column

[adarsh2608]

Hello I need to  summarize the excel (attached) here it should be group by year, and group 

expected o/p as below. it should sum the values and update it in the each column respectively for year and group . values and year are dynamic which may change every year . year mentioned in the excel is year and month as there is no date available . also need to calculate last 30 days ,90 and 120 days even if the there is no data it is fine

GROUP

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

Last 30 Days

Last 90 Days

Last120 Days

Direct Fund

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Asset

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Mutual Fund

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of value

Tech Support

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

Sum of (value )

[IraWatt]

Hey @adarsh2608,

Ive attached a workflow to show you how to do this, ping if you have any questions and green tick if it solves your problem ;) 

All the best,

Ira

[IraWatt]

If you wanted to fill in the NULLs you can use a Data Cleansing tool. 

[adarsh2608]

Thank you for the reply

but if there is no value matching available for last 30 , 90 or 120 days then it is only showing null value in cross tab session . if there is no last 30 days or 90 days it should give 0

[IraWatt · Accepted solution]

I've attached an example of how to get round this problem, Hopefully sorts all the issues.

[IraWatt]

To further clarify how this works. If you want to change the brackets first change this formula tool in the highlighted areas:

Then make sure to update the cross tabs here:

All the best,

Ira

[adarsh2608]

Hello Ira
There is one more requirement where last 30 ,60,90 and 120 days should show the month based. as in the year column there is only month and year .

so in last 30 days it should show data of entire previous month ,even if current day is 1st of May it should show all the data of April from 
and similarly if it is last 60 days it should show last 2 months data and 90 days is last 3 months data