Help needed with expressions in the table

[#1]

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

[pokassov · Accepted solution]

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...

[#3]

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+