Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm running into an issue , when trying to create table view with calculated rows and calculated columns.
Here is an example:
As you know, green area is not an issue, since it can be done through Calendar dim. Red one is the one I'm struggling with since Change and Change,% need to calculated on the fly based on Selections. Am I missing something?
I have tried to surf web but found close to nothing, if you happened to know good resources, please share.
Looking forward for your inputs.
Dmitry
Hello!
There are some questions about your issue.
Is year a dimension?
If the answer Yes then: What a behavior do you expect for situation when users select more than 2 years. I mean columns Change and Change%.
If we are talking about only 2 selected years then we can use following approach:
1. Create table for "dimensions"
t1:
LOAD * Inline [
Dim
Avg Mbr Count
Cost Per Member
Cost Per Contract
];
2. Add 4 expressions
For example:
Lable: =min(Year)
Definision:
if(Dim='Avg Mbr Count',count({$<Year={$(=min(Year))}>} MemberName),
if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} MemberName),
if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} Contract))
)
)
etc...
Hello!
There are some questions about your issue.
Is year a dimension?
If the answer Yes then: What a behavior do you expect for situation when users select more than 2 years. I mean columns Change and Change%.
If we are talking about only 2 selected years then we can use following approach:
1. Create table for "dimensions"
t1:
LOAD * Inline [
Dim
Avg Mbr Count
Cost Per Member
Cost Per Contract
];
2. Add 4 expressions
For example:
Lable: =min(Year)
Definision:
if(Dim='Avg Mbr Count',count({$<Year={$(=min(Year))}>} MemberName),
if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} MemberName),
if(Dim='Cost Per Member', sum({$<Year={$(=min(Year))}>} CostValue)/count({$<Year={$(=min(Year))}>} Contract))
)
)
etc...
Hello. Thank you for your quick reply, Sergey. Provided clue is right on the money.
Exact behaviour and details were to much to explain but your answer is a huge help. A+