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

[dplr-rn]

depends on your criteria. 

but roughly

if(yourcriteria,yearamountexpression,lifeamountexpression)

[sush]

Well, I tried it but it's not working on the scenario which I am handling. I have a year month filter and i want to calculate Life to data values for certain account types and Year to date values for certain types. 

So in this scenario, if(yourcriteria,yearamountexpression,lifeamountexpression) is not working as expected.

I am using a set modifier in my expression, if I use that then the values are not coming correctly. If I use a set modifier along with if based on the below expression it is taking only the values from the if part and not considering the else part.

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

sum({<[Year]=,[FYMDate.autoCalendar.YearMonth]=,CalRowNo=,
[Year]={"=$(=max([FYMDate.autoCalendar.Year]))"},
[FYMDate.autoCalendar.YearMonth]={"=$(=max([FYMDate.autoCalendar.YearMonth]))"},
[CalRowNo]={"<=$(=max([CalRowNo]))"}
>}[Amount])
)

Here CalRowNo is an integer field in the calendar table. I am filtering all the records since the beginning of the transaction.

[dplr-rn]

not completly clear.

i thought you had both these expressions in the same table. you could try this too. 

If ([AccountType] = 'A', column(2), column(3))

[sush]

Ok here is the scenario

I have records similar to below

Note: Fiscal Year - April - March. So for example, current month Aug 20 will be of Fiscal Year - 2021 and month will still be same - August

Date Table

Fiscal year	Fiscal Month	Company Code	Type	Amount
2021	Jul	A	B	100
2021	Jun	A	B	100
2021	Jun	B	A	200
2020	Mar	A	B	10
2020	Mar	C	A	100
2020	Aug	D	A	100
2017	Oct	A	B	100

Calendar:

Cal Row no	FiscalYear	FiscalMonth	fMonth(Numeric)	Cal Month	Cal Year
1	2017	Apr	1	4	2016
2	2017	May	2	5	2016
3	2017	Jun	3	6	2016
.
.
50	2021	Jun	3	6	2020
51	2021	Jul	4	7	2020
52	2021	Aug	5	8	2020

to be clear - 

Amount - this is the column which I will be using based on the Account type and the calculations are based on Fiscal Year

Account type will be either - A or B. 

If Account type = A - calculate - Life to Date (Amount)

If Account type = B - calculate - Year to Date (Amount)

Now I used table/pivot in my charts to display the values.  I have a filter to filter Fiscal Year, Month. If we select the value as 2021-Jul then it should display the values of the amount in a single column based on Account type - Life to date/Year to date.

I am able to get Life to date and Year to Date as separate columns and the total is appearing fine without any issues if i use them as separate columns. I have used the expression with the set modifier which I mentioned earlier.

The problem is when I want to display a single column as Amount. If the Account type is A then display Life to date Amount, if B display Year to date Amount. When I tried to use "If" in the expression its not displaying as expected.

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

sum({<[Year]=,[FYMDate.autoCalendar.YearMonth]=,CalRowNo=,
[Year]={"=$(=max([FYMDate.autoCalendar.Year]))"},
[FYMDate.autoCalendar.YearMonth]={"=$(=max([FYMDate.autoCalendar.YearMonth]))"},
[CalRowNo]={"<=$(=max([CalRowNo]))"}
>}[Amount])
)

Here CalRowNo is an integer field in the calendar table. I am filtering all the records since the beginning of the transaction.

Please let me know if I am making this clear. If so could you please provide suggestions on how we can achieve this.

[Kushal_Chawda]

If you individually use the expression, do you get correct values?

[sush]

Yes if I use the expression individually I am getting correct values

[Kushal_Chawda]

Where you are using this measure? What is dimension? Is there any other measure you are using? Ideally this  should work if you are using AccountType in dimension. If account type is not in dimension then you can try using below

sum(aggr(YourExpression, AccountType))

[sush]

I don't have any other measures. I have other dimensions added to the table - Company Code, Account No, Cost Center, Profit center.

What I noticed is if I add the Profit center dimension to the table is when it's not calculating correctly. Otherwise, it's fine. Is there anything I need to do this to get this work correctly

I also tried with the below suggestion but it's still not correct

sum(aggr(YourExpression, AccountType))

[Kushal_Chawda]

ry including other dimension in aggr like below

sum(aggr(YourExpression, AccountType, Profit centre))

[Kushal_Chawda]

sum(aggr(YourExpression, AccountType, Profit centre))