Get cube values for first and last element of selection

Hi,

I need to show in a data view a column with the values related to the first element of a selection.
I also need a column with the value for the last element.
For example, the selection is for the months from January to June. I want the values for January.

I used the ANALYTICAL FUNCTIONS > First Value, setting the From dropdown to "First selected period" and the To dropdown to "Last selected period".

This works when the cube has values for January, but when for a specific item it is empty i get the first non-empty value from the next month.
For example, if the cube for a specific item doesn't have values for January and February I get the value for March, while I would like to have 0 (zero) in the cell.

All the dimensions of the cube (except for the time entity) are sparse.

Thanks
Fabiano

------------------------------
Fabiano Martino
Senior Developer
A.F.I.S. G. CLERICI SPA
Italy
------------------------------

Find more posts tagged with

Configure

Accepted answers

All comments

Helmut Heimann

Hi @Fabiano Martino,

when you're talking about the configuration in a dataview and apply a selection of Jan..Mar, you'd like to see the columns Jan..Mar, correct?
How'd you configure the display options in your dataview? And how is the column setting?
From my point of view, that should work if you apply the following settings.

Axis:

Block configuration:

Kind regards
Helmut

------------------------------
Helmut Heimann
Senior Consultant
Board Community
Germany
------------------------------
-------------------------------------------

Fabiano Martino

I'll elaborate to explain my request better.

I have three cubes:
- Warehouse stock, with dimensions item and month. This cube is semi-addictive, since it can not be summed for the month dimension.
- Purchases
- Sales

My goal is to create a data view representing the initial state, the changes and the final state of the warehouse.

The user selects the month range for the analysis (eg from January to June).
The first column of the data view should take the warehouse stock for January only (which represent the initial value of the warehouse).
The same goes for the last column, which should take only the stock for June.

Between this two columns I need the Purchases and Sales for the entire selection period (eg from January to June).

I checked my settings and they are exactly as you showed me.

The problem I'm facing is getting only the first month for the warehouse stock, while getting the total values for the purchases and the sales.

I'm considering the creation of separate cubes for the stock, with a specific dimension for the month.

Thanks
Fabiano

------------------------------
Fabiano Martino
Senior Developer
A.F.I.S. G. CLERICI SPA
Italy
------------------------------
-------------------------------------------

Stefan Haag

Hi

I have the same issue with the function "last value" in the "Analytical Functions". If the last value of a selected period is "0", it will just take the first previous value that is not "0".

E.g.:

Selected Period: Oct - Dec
Values in Months
- Oct: 10
- Nov 5
- Dec 0
Now "last value will bring 5 instead of 0.

Would be very good to have the option to say that "0" should not be ignored so that the "last value" presented is "0".

I was alternatively trying to solve this with the "Time Functions" using "Last value", but also there "0" is ignored.

Regards,

Stefan

------------------------------
Stefan Haag
Head of Finance
Adcubum AG
Switzerland
------------------------------
-------------------------------------------

Julien CARDON

We have the same issue and this one is really frustrating when you can't do the select you want...

- analytical functions does not help because it is not compatible with some other features like rule or time function.
- it should really be a time function called "last month of the select" , or "first month of the select" (when in your dataview you have several month selected)-------------------------------------------