Forum Discussion

zfesler's avatar
zfesler
Data Storage
05-09-2022
Solved

Tracking previous months data alongside current month

 

Morning!

Is there a way that I can track the previous month's exceptions alongside the current month? The left widget is working as intended with the max value listed being the total exceptions of the previous month, and the ~46k is the current exceptions for the month. 

I was hoping that on the widget on the right, that I could track the previous month's exceptions so that we can see where we are currently trending in relation to the same time frame from the previous month. 

dashboards & reporting
  • KatieG's avatar
    KatieG
    05-11-2022

    Hi zfesler - I think something like this could work:

    The solution is a combination of an additional column in the model and a built in function we can use in designing the widget:

    In the model (live or elasticube)

    Add a custom column to your date dimension table that determines whether the date is included in the month to date (MTD) calculation (where [Date] is the date column)

    -- DateDiff column
    CASE
      WHEN GetDay([Date]) <= GetDay(Now()) THEN 1
      ELSE 0
    END

    This will act as a flag for whether to include the data or not

    In the widget/dashboard:

    For this example, I have data for:

    • March 2022: Daily profit $1
    • April 2022: Daily profit $5
    • May 2022 thru May 10th: Daily profit $10

    To design the dashboard - I added a date filter of Timeframe "This Month"

    "This month to date" widget is just total profit

    "Last month to date" uses the PASTMONTH formula

    And a widget level filter of DateDiff = 1

    This results in a dashboard that looks like this:

    Creating a pivot table with the same data and flag shows this method seems to be accurate

    Caveats and considerations:

    Consider:

    • Query Path: You should verify that this change does not cause an M2M relationship (in this reply I focus on adding the flag logic in the dimension but it depends on what your model looks like). Check out the query using the JAQLine plugin to see what the resulting query path is 
    • Data frequency: This solution assumes live query or that data models are built on some frequency (probably at least daily).

    Hope this helps!

4 Replies

Replies have been turned off for this discussion
  • david-h's avatar
    david-h
    Cloud Apps
    05-09-2022

    Hi,

    Did you try "pastyear" function + setting the current month filter on the dashboard level ? You can use quick functions from the menu or PASTYEAR function in the formla editor. Or I did not understand what you want to achieve ...

    Best, David.

  • zfesler's avatar
    zfesler
    Data Storage
    05-09-2022

    I probably didn't explain myself fully.

    I'm wanting the widget on the right to show me the running total of last month's exceptions in order to visually see if we are trending behind or ahead of where we were at exactly 1 month ago. 

    The widget on the right will start from 4/1/22 and increment up from there as we progress through the month of May (so as of today it would be the Sum[Exceptions] from 4/1/22 through 4/9/22). However, when June roles around, I'm wanting it to change and show May's data.

    This is how I'm essentially wanting it to show up, but I have to go into the widget and adjust the date range manually as of now. The ~195k for the max on the "Current Month" is the grand total from the previous month of April as well.

    I hope that makes more sense.

    Thanks for your input!

     

  • KatieG's avatar
    KatieG
    Sisense Employee
    05-11-2022

    Hi zfesler - I think something like this could work:

    The solution is a combination of an additional column in the model and a built in function we can use in designing the widget:

    In the model (live or elasticube)

    Add a custom column to your date dimension table that determines whether the date is included in the month to date (MTD) calculation (where [Date] is the date column)

    -- DateDiff column
    CASE
      WHEN GetDay([Date]) <= GetDay(Now()) THEN 1
      ELSE 0
    END

    This will act as a flag for whether to include the data or not

    In the widget/dashboard:

    For this example, I have data for:

    • March 2022: Daily profit $1
    • April 2022: Daily profit $5
    • May 2022 thru May 10th: Daily profit $10

    To design the dashboard - I added a date filter of Timeframe "This Month"

    "This month to date" widget is just total profit

    "Last month to date" uses the PASTMONTH formula

    And a widget level filter of DateDiff = 1

    This results in a dashboard that looks like this:

    Creating a pivot table with the same data and flag shows this method seems to be accurate

    Caveats and considerations:

    Consider:

    • Query Path: You should verify that this change does not cause an M2M relationship (in this reply I focus on adding the flag logic in the dimension but it depends on what your model looks like). Check out the query using the JAQLine plugin to see what the resulting query path is 
    • Data frequency: This solution assumes live query or that data models are built on some frequency (probably at least daily).

    Hope this helps!

    • zfesler's avatar
      zfesler
      Data Storage
      05-11-2022

      Thank you KatieG! This is exactly what I was trying to accomplish.