add or substract measures in a crosstab

[sergiord2016]

Hi ,

I have a crosstab with several measures in the columns and one dimension in the rows:




I have also a filter  on the report in order to select the year.

I want the user to select the year and the report should show the number of sales of the year selected and the previous year selected. In the example , the user selected 2017 in the filter so i have one filter in the query that is the parameter year -1.

I have done this , but the problem is that i cant do the substraction of one year and the other because the dimension are not members , (702-611)=91.

I think one possibility is to calculate a member with the filter but i dont know how....

In my date dimension member 0 is actual year , -1 previous year... if i could calculate the member with my filter the problem should be solved but , how can i do this?

Thanks in advance.

[MFGF]

Hi ,

I have a crosstab with several measures in the columns and one dimension in the rows:




I have also a filter  on the report in order to select the year.

I want the user to select the year and the report should show the number of sales of the year selected and the previous year selected. In the example , the user selected 2017 in the filter so i have one filter in the query that is the parameter year -1.

I have done this , but the problem is that i cant do the substraction of one year and the other because the dimension are not members , (702-611)=91.

I think one possibility is to calculate a member with the filter but i dont know how....

In my date dimension member 0 is actual year , -1 previous year... if i could calculate the member with my filter the problem should be solved but , how can i do this?

Thanks in advance.

Hi,

The solution will differ depending on whether you are using a relational package or a dimensional package. Can you advise which you are using here?

MF.

[sergiord2016]

We actually have both models created , dimensional or relational.

In this case the measure and the dimension are draggeg from dimensional model , but if relational solution is better we can use relational model.

Before now i have been always used teh dimensional modeling because i have to make always the comparison between one year and the previous year.

I only know how to do this comparison dragging the members that correspond to one year and previous year.

Now i have the problem that this report is not static , has a user filter so the static member can't be used , i have probed with data element calculating one year and previous year and using them as dimensions but it does not work.

Hope this will help in your answer. Thanks in advance.

[MFGF]

We actually have both models created , dimensional or relational.

In this casi the measure and the dimension are draggeg from dimensional model , but if relational solution is better we can use relational model.

Before now i have been always used teh dimensional modeling because i have to make always the comparison between one year and the previous year.

I only know how to do this comparison dragging the members that correspond to one year and previous year.

Now i have the problem that this report is not static , has a user filter so the static member can't be used , i have probed with data element calculating one year and previous year and using them as dimensions but it does not work.

Hope this will help in your answer. Thanks in advance.

Hi,

If you have a dimensional package, this is pretty easy.

First, remove your detail filter - these are a relational concept and could easily deliver incorrect results with a dimensional package.
Next, remove the Year level from your crosstab row headings.
Drag a new query calculation into the rows area of your crosstab, and use a dimensional expression to prompt for the year you want to see, using an expression of:
<your Year level> -> ?Your Year Parameter?
Next drag a second query calculation below the existing calculation in the row headings. Use a dimensional expression to return the previous year:
prevMember(<your Year calc from above>)
You can now add a third calculation that subtracts one from the other.

Cheers!

MF.

[sergiord2016]

Thank you very much.

It works!!!!!