Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
depends on your criteria.
but roughly
if(yourcriteria,yearamountexpression,lifeamountexpression)
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.
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.
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))
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.
If you individually use the expression, do you get correct values?
Yes if I use the expression individually I am getting correct values
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))
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))
ry including other dimension in aggr like below
sum(aggr(YourExpression, AccountType, Profit centre))