Cognos Analytics with Watson

 View Only
  • 1.  Current Months WRT to Year as columns in cross tab

    Posted Thu June 02, 2022 07:35 AM
    Hi,
    Need to help in figuring these out, I'm using a dimensional data source for the crosstab report.
    Cross Tab, Columns needed to the current month and year like 2022-01,2022-02,2022-03......2022-12 these months columns needed to be updated dynamically every year with respect to the current year.
    As of now, getting all the months from the hierarchy 
    needed all months for current year as columns in the cross tab, No prompt values are included.



    Above are pulled from current year quarters,these won't work for next year so needed dynamically to change months and year.
    Needed help.

    Thanks.

    ------------------------------
    P S
    ------------------------------


  • 2.  RE: Current Months WRT to Year as columns in cross tab

    IBM Champion
    Posted Thu June 02, 2022 07:42 AM
    Hello,

    which cube technology do you use? Most of them should have the ability to create dynamic relative time mebers in the time dimension.
    Example in one of our dynamic cubes:


    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------



  • 3.  RE: Current Months WRT to Year as columns in cross tab

    Posted Thu June 02, 2022 07:51 AM
    Hi Robert,
    Thanks for the reply
    As per my knowledge, we are using DQM Modelling.
    The cube consists of the current year and month members too, but we can't only pull current months, years as static columns right?

    ------------------------------
    P S
    ------------------------------



  • 4.  RE: Current Months WRT to Year as columns in cross tab

    IBM Champion
    Posted Thu June 02, 2022 07:54 AM
    Hi P S,

    look at @Paul Mendelsons post below. You need to identify your current year and then apply the children/descendants function to it. This will return the dynamic members of "current year".


    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------



  • 5.  RE: Current Months WRT to Year as columns in cross tab

    IBM Champion
    Posted Thu June 02, 2022 07:45 AM
    There are several ways of doing this, but the most important thing here is being able to identify the current year.

    Once you can identify the current year, you just need to do 
    descendants([currentYear],[Cube].[Date].[Date Hierarchy].[Month]) (assuming you have a straight time dimension, and don't have anything weird in it)


    First, what type of cube is this? Do you need it to be the current date as of the system time today, or should it be based on data? Can you paste in an example of one of the year MUNs?

    ------------------------------
    Paul Mendelson
    ------------------------------



  • 6.  RE: Current Months WRT to Year as columns in cross tab

    Posted Thu June 02, 2022 07:58 AM
    It's based on the data




    ------------------------------
    P S
    ------------------------------



  • 7.  RE: Current Months WRT to Year as columns in cross tab

    IBM Champion
    Posted Thu June 02, 2022 09:40 AM
    Looking at the level you can't simply pull the tail (unless you know that it will always be the second to last year that you want). If you can pull that member, you can do this:
    current year: item(tail([Cube].[Date].[Date Hierarchy].[Year],2),0) 

    If it needs to be based on measure data, do something like this:
    item(tail(filter([Cube].[Date].[Date Hierarchy].[Year],[Measure] is not null)),0)

    Note, in this case filter shouldn't be too much of an issue since you're only looping over 21 members, but in general I strongly recommend against using the filter function like this.

    Right click on one of the members in the list and show properties. I'm interested in seeing what the memberuniquename looks like:
    This is an example from one of my cubes
    If we can base the current year on the system time, we can do something like this:
    #'[REPORTING].[R_TIMELINE].[R_TIMELINE].[Fiscal Year]->:[TMR].[R_TIMELINE].[R_TIMELINE].['+timestampMask(_add_days($current_timestamp,-7),'yyyy')+']'#​

    That returns the current year as of the date 7 days ago. It's significantly faster than doing a filter function (and even a few milliseconds faster than using the item(tail()) combo, but you have to construct the code to match the member perfectly.​

    ------------------------------
    Paul Mendelson
    ------------------------------