Get cube values for first and last element of selection

Fabiano Martino
Fabiano Martino Customer
5 Up Votes First Comment First Anniversary
edited June 2022 in Platform
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
------------------------------
Tagged:

Answers

  • Helmut Heimann
    Helmut Heimann Employee, Community Captain
    250 Likes Fourth Anniversary 100 Up Votes 100 Comments
    edited June 2022
    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:
    imageBlock configuration:
    image
    Kind regards
    Helmut

    ------------------------------
    Helmut Heimann
    Senior Consultant
    Board Community
    Germany
    ------------------------------
    -------------------------------------------
  • Fabiano Martino
    Fabiano Martino Customer
    5 Up Votes First Comment First Anniversary
    edited June 2022
    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
    Stefan Haag Customer
    Third Anniversary 25 Up Votes Advocate First Comment
    edited June 2022
    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
    Julien CARDON Customer
    Fourth Anniversary 25 Likes 10 Comments Level 100: Foundations of Building in Board
    edited June 2022
    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)-------------------------------------------