Till Date calculation in Incorta

[msinha8]

Hi All,

Just looking for an option in which we can do a Till Date Calculation in a Pivot table.

i.e. 

For Example for a company "X" we have data for every months (measure column : Amount)  from Mar-2022 and if I am running the report in May-2024, then my reports should show amount distribution like :

Mon-YY	Mar-22	Apr-22	May-22	…	Dec-22	Jan-23	…	Jan-24	..	May-24
Company
X	100	100(Mar amount) + 60 (april amount) i.e. 160	160 (previous month amt)+100 (May amount) i.e. 260		amount till Nov-21+ amount of Dec-22)	amount till Dec 2022+ Jan23 amt		amount Till Dec-23 + Jan-24 amt		amount till Apr-24+May-24 amt

and this calculation will repeat for every company.

Regards,

MS

[anurag · Accepted solution]

@msinha8 have you tried using running total for a measure?

You will need to do that using Resultset.

1. Create resultset with aggregated table

2. Add company and first date of the month in dimension

3. Add sales column in measure

4. Enable Running total for sales and save resultset

5. use this resultset to design your pivot table

This video shows how to use Resultset:

https://www.youtube.com/watch?v=06BKZYS-xg8

[Shashidhar]

Hi @anurag ,

I have a little confusion on this running total using result set. As we have "Running total" option under measure to get cumulative sum right? Why we need to enable running totals option in result set and use the same column on aggregated insight. can you please clarify?  is there any logic behind it?

Thank you in advance.

Regards,

Shashidhar.S

[JoeM]

@msinha8 - Did @anurag 's response help your solution?

[msinha8]

@JoeM  Yes, @anurag 's response was helpful and pointed me in the right direction.