Read Column value from table

[sush]

Hi,

I have a table which has 2 columns (Year Amount, Life Amount). These 2 are calculated columns based on the formula in Set Analysis.

Now I want to add the third column - Amount which shows either Year Amount or Life Amount based on Criteria. Could you please let me know how can we achieve this?

Thanks

[sush]

I tried with this but still not showing the totals correctly. Everything is showing as 0's

[Kushal_Chawda]

Would you be able to share sample app?

[sush]

Sure I will create with some dummy data and will provide you an app.

I am sorry, another thing I noticed is the problem comes when I add the If condition. I tried with the below

If ([AccountType] = 'A',
        SUM({1<[CalRowNo]={">=1<=$(=max(CalRowNo))"}>} [Amount]) 

,0)

If I add the expression similar to above, even if the AccountType='A' records are getting 0 for some of them but historically it has values.  This is displaying incorrect totals when I have ProfitCenter in the table. The same expression works fine in the below scenario

1. With Profit center (display in the table), without if condition in expression.

2. No Profit center (display in the table), with if condition in expression.

Note: These are with the Filter value selected for Year-Month.

As I mentioned earlier, the data that I have does not have records for all year and month. So I added another data set with Account, CompanyCode Year, Month with the amount as 0's. This is to display totals for Life to date until the period selected in the filter. 

Is there anything I need to look to see the issue or to make it work? Please let me know

[sush]

I have attached the App and the sample data file.  Please review the same and let me know.

Note: For this App I have not added the calendar and so you will see the formula based on the date which is present in the date field. Here the FYMActDate and year have the Year as Fiscal Year.

TB_Amount column has the expression which says either LTD Amount calculation or YTD Amount. I have added another column just to highlight the expected output for the filter - 2021-Jul.

[Kushal_Chawda]

how you are getting 2600, 1300 &2110 for account type P as  for that account YTD_expression should work?

[sush]

I am sorry it has to be the value 600 for that account. Instead of taking the values from YTD column I took the LTD column by mistake while adding manually.

for account type P I have to display the value 600 in the TB amount column.

[Kushal_Chawda]

see the attached

[sush]

Thank you so much for your response. I really appreciate your help. This seems to be working nicely with the sample dataset. I will make changes to my actual dataset, will validate, and keep you informed.

Got a couple more questions, with the same data set and formula that we applied. Could you please let me know how can I achieve this?

1. How can I show the data for the last n months?

Something like below - here the filter value for Year-Month Selected is - 2021-Aug

2. How can I show data for the Year-Month selected

Something like below - for all prior-year month trend- here the filter value for Year-Month Selected is - 2021-Aug

Thank you.

[Kushal_Chawda]

assuming you need last 6 months. try below calculated dimension

=aggr(only({<FYMActDate.YearMonth={">=$(=date(addmonths(max(FYMActDate.YearMonth),-5),'YYYY-MMM'))<=$(=date(max(FYMActDate.YearMonth),'YYYY-MMM'))"}>}FYMActDate.YearMonth),FYMActDate.YearMonth)

[sush]

It's not working when I add to the same report 

What I need is to display the data for the last 6 months.  For example, if I select the filter value as 2021-July. I want to display the amount for July and the past 6 months prior 

AcctType	Company	Profit Center	cost center	2021-Jul TBAmount	2021-Jun TBAmount	2021-May TBAmount	2021-Apr TBAmount	2020-Mar TB Amount	2020-Feb TB Amount