Total & Remaining Amount based off specific date

[MalcolmCICWF]

Could anyone lend me some help/advice on laying out my data in the below format and example? I am not sure on the best way to go about this via Dimensions and Expressions within a Straight or Pivot table...

I have a couple cyclic groups that my end users would like to sort on. They need a totals column as seen by "Remaining ERCs as of (date)", and then they need ERC Month/Year laid out horizontally to show just how much ErcAmount remains in each.

If I can achieve this, I also need to find a way I can select or enter which ERC Month/Year the sum of the Totals begin In my example it is 1/1/2016, I would like to be able to change that to any date... Thoughts?

[sunny_talwar]

This seems like an easy pivot table with 3 dimensions

1)1st Cycle Group -> Enable Partial Sums on the presentation tab

2) 2nd Cycle Group

3) ERC Month -> Enable Partial Sums on the presentation tab

For the 2nd requirement, it would be easy if you can share a sample with us with your expected output so that we can help you better

[MalcolmCICWF]

First off, thanks for all your help recently Sunny. I ended up just going with a Pivot Table layout, but my main question is still how I can select the start and end dates being displayed within the  table. I would like to do this based on the calendar selections I have added, but I do not have a lot of experience with them.

I am guessing you would use a calculated dimension to display based on your selections?  I have include a limited example of the data. I hope this helps.

[sunny_talwar]

So are you saying that if you select Dec 2009, the pivot table should show you everything starting Dec 2009 till max date available in the application?

[MalcolmCICWF]

That would be ideal yes, The idea is to have the totals reflect the starting date to the max date and show us the estimated return between that time frame. If I could select the start AND the end date, that would also maybe be helpful, but at least being able to select the start, would be... well... a start.

[sunny_talwar · Accepted solution]

Try this expression:

Sum({<ErcDateMY = {"$(='>=' & Date(Min(ErcDateMY), 'MM/YYYY') & '<=' & Date(Max({1}ErcDateMY), 'MM/YYYY'))"}>}ErcAmt)

[MalcolmCICWF]

Thanks, that seems to work for what I need, I have little experience with set analysis though so I am not really sure how each part is working

[sunny_talwar]

Lets take some time and try to understand the syntax here.

1) Sum(ErcAmt) is the base expression, but this would filter based on selection. What are we looking for? To be able to see everything from what's selected to what's available

2) ='>=' & Date(Min(ErcDateMY), 'MM/YYYY') if you add this expression to a text box object you will see that this will be showing >= your selected date and in the same format as your date field. So important thing to recognize here is that the format is very important for set analysis to work. If format isn't the same your set analysis won't work Dates in Set Analysis

3) ='<=' & Date(Max({1}ErcDateMY), 'MM/YYYY') now this in a text box would give you your upper limit and in the same format as your date field. In your case, this condition may not even be required because you are looking for everything above the minimum to the max. But in cases where there is upper and lower limit like for instance last 1 year of data from the selected date etc, the upper and lower are both needed

4) Combining the two limits into the set analysis

{<Date = {"$(=YourUpperAndLowerLimitExpressionInCorrectFormat)"}>}

I hope this will help you . I am also attaching a decent guide for you to learn from set analysis.

Best,

Sunny

[Saravanan_Desingh]

Hi sunindia‌

I have tried using the equivalent $-expansion instead of your expression. But it is not working.

Can you please advise, whats wrong here?

=Sum({<ErcDateMY = {">= $(=Date(Min(ErcDateMY), 'MM/YYYY')) <= $(=Date(Max({1}ErcDateMY), 'MM/YYYY')))"}>}ErcAmt)

Thank you.

[Saravanan_Desingh]

Hi sunindia‌

I found the issue. Anyway thank you

=Sum({<ErcDateMY = {">=$(=Date(Min(ErcDateMY), 'MM/YYYY')) <=$(=Date(Max({1}ErcDateMY), 'MM/YYYY'))"}>}ErcAmt)