Skip to Content

MTD and YTD in Crosstab

Hello... I'm sure this has been discussed in other threads but I have not found the right solution.

Challenge: A cross tab with locations as rows, type as column and person count as the body. This works fine if running per month but I need a MTD value and a YTD. The YTD could be in a separate cross tab if needed.

The values are not stored in the Universe and cannot be added. The counts are a simple calculation of the =count([person]) field. How can I get both to return on the report?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 11 at 02:21 PM

    Hi,

    Do you have a Month/Year prompt?.

    Try this

    for MTD:=Sum(If(Month([Userresponse()])=Month(CurrentDate()) ; [measure]))

    for YTD: =Sum(If(Month([Userresponse()])=Year(CurrentDate()) ; [measure]))

    Use the above formula in place of Measures within cross tab.

    share a screen shot or data sample for better solution.

    Thanks,

    Jothi


    Add comment
    10|10000 characters needed characters exceeded

    • I do not have a UR. The report is refreshed using a date prompt, "Last Month", "Month to Date", "Week to Date", etc. Selecting the date needed returns data. I'll need to look into the use of a UR.

  • Oct 11 at 03:39 PM

    Hi,

    Userresponse() will display your prompt value selected by User.

    You can create variables to display MTD, YTD.

    What exactly is your requirement if you have MTD , YTD and Prev Month already in place?.

    can you post a sample data what you are looking for?.

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 12 at 02:55 PM

    Hi,

    You already have variables to bring in MTD and YTD. Use those variables with in the below function..

    =Userresponse("description of Variable" or [Technical Name of Variable])

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded