Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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])))
Hi Martin,
Example of table in attached file.
Regards,
Andrey
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())
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!
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])))
Excellent Omar! There I was able to get the results I needed!
Thank you very much for helping Omar and Andrey.
Regards!