Get quarter value through accumulated value

[martin_caruso]

Hello community, how are you?

The following problem occurred to me. I have a base for each quarter, which I am concatenating as they are loaded until a single table remains.

The totals of each quarterly amount are accumulated so that in order to obtain the value of the amount for that quarter I must calculate it.

To do this assemble the following table, so that my need can be understood.

YEAR - QUARTER	TOTAL VALUE	VALUE QUARTER	EXPRESSION
201503	2400	2400	201503
201504	5300	2900	201504 - 201503
201601	7900	2600	201601 - 201504
201602	11000	3100	201602 - 201601

In the first column is the period that specifies the YEAR and the QUARTER. In the second column, the value of the accumulated amount for each quarter. Here I make a couple of clarifications: when the quarter is 2 (for example, 201602) the accumulated is also the annual accumulated. The other point to note is that the first quarter, that is, the quarter that only accumulates "its" quarter is 3 (for example, 201503). The remaining two columns represent the value that should be the amount of each quarter and how it is calculated.

The indicators that I must develop must show the amounts of the current and (and selected) quarter and the previous quarter and year. For this, I have on the dashboard a selector of YEAR and a QUARTER.

The expression I am using, is the following but I am given 0.

= sum(DIM_TOTAL_VALUE) -

sum({$ < DIM_YEAR={$(=DIM_YEAR_PREV)}, DIM_QUARTER={$(=DIM_QUARTER_PREV)} > } DIM_TOTAL_VALUE)

Does anyone have any idea how to replicate the table I need?

I appreciate the help you can give me.

Regards!

[ahaahaaha]

Hi Martin,

Example of table in attached file.

Regards,

Andrey

[OmarBenSalem]

If you do have a value quarter for each quarter and you want  a table for example in which you'll have:

as a dimension: Quarter

as columns:

1) value of each quarter : sum(Value)

2) accumulated value   : rangesum(above( sum(Value),0,rowno())

[martin_caruso]

Hi Andrey, thanks for your response and example.

Unfortunately it is not what I need. The table with which I count, among many fields is the following:

YEAR - QUARTER	TOTAL VALUE
201503	2400
201504	5300
201601	7900
201602	11000

Through this table I have to obtain the quarterly values ​​(Column QUARTER) that are obtained from the expression (Column EXPRESSION).

When I want to replicate what happened to Andrey, I get the following and all I did was modify the name of the dimension:

Always the quarter ending in '03' (Example: 201403,201503, 201603, etc) will have the same value for TOTAL VALUE as for VALUE QUARTER. If the first record is 201504, you should not apply the RowNo () = 1.

I appreciate your response and predisposition to help me solve this.

Regards!

[OmarBenSalem · Accepted solution]

You have the accumulated total and want to have the value in each quarter?

If so, do as follow:

if(RowNo()=1,sum([TOTAL VALUE]), sum([TOTAL VALUE])- above(sum([TOTAL VALUE])))

[martin_caruso]

Excellent Omar! There I was able to get the results I needed!
Thank you very much for helping Omar and Andrey.
Regards!