Dynamic time: Possible to show previous month that contains data?

• 

  Ole Dyring

  November 09, 2022 08:19

  Hi Mads,

  I have an example where I created a crosstab and added a User dimension with two dynamic members: Month -2 and Month -1. These are the two columns in my screenshot:

  

  Then I added a visibility agent with this condition:

  allcount(all, d1, m1) = 2 and allcount(d1:0, d1, m1) = 1

  ... and made sure that the Action was to hide members of my User dimension.

  In cases where both columns contain data, the agent will hide the first column (Month -2).

  Example, opening the table in November 2022:

  

  In cases where only one column contains data, the agent has no effect.

  In my demo data I have data until December 2022. Simulating a date in February 2023, the Month -1 column (January 2023) will have no data, and then the Month -2 (December 2022) will become the only column visible.

  Example, opening the table in February 2023:

  

  BR / Ole

  0
• 

  Niels Thomsen

  November 09, 2022 08:27

  Hi Mads

  It can be done with a workaround.

  If you have a table like this - with month on the horizontal axis (the vertical axis has no importance to this example - but I'll just put Departments there as an example):

  

  In my table a filter with previous 12 months has been applied - but the most recent data is from August as you can see.

  Now I add a visibility agent to hide everything but the last column:

  1. choose to Hide Member:

  

   

  2. Condition should be something like this:

  

   

  3. And the action needs to be moved from the standard setting (to hide rows) to hide something on the vertical axis (in my case YMD member):
  

   

  Now you'll have the last month with data in the table:

  

   

  This is obviously a workaround - if you want to promote the idea that TARGIT should be able to autodetect the last month with data and use Dynamic Dates based on this, it would be great if you would make a feature request right here in the community: https://community.targit.com/hc/en-us/community/topics/7278561562141-Feature-requests-Ideas

  0
• 

  Mads Høgenhaug

  November 09, 2022 08:27 (Edited November 09, 2022 08:31)

  Thanks a lot, Ole and Niels! That oughta do the trick

  0
• 

  Mads Høgenhaug

  November 14, 2022 15:21

  Hey Niels Thomsen.

  When I try to implement your solution it works the way you showed it. In my case, the months are on the y-axis, while the Departments are on the top (Not my decision 😅) , so I changed your formular a bit to accommodate for this: allcount(allcount(0, d-1:0, m1) > 1.   
  The above function hides all the rows (months) except the last one in the table. However, it also removes all the children (dates) from the last month, except the final day of that month. 

  Is there a way, when unfolding the remaining month, to show all the dates - and their respective data - instead of only the last day of the month? 

  0
• 

  Ole Dyring

  November 21, 2022 08:39

  Hi Mads,

  If your time dimension is a real hierarchy, e.g., a 3-level hierarchy with Years, Months and Dates as level 1, 2 and 3 respectively, you can modify your visibility agent like this:

  • allcount(d1, d-1:0(l2), m1) > 1

  In this way I am forcing the count calculation to work on level 2 (l2), the Month level.

  If you just put together two dimensions, Months and Dates, on the vertical axis, you may need to do something like this instead:

  • allcount(d1, d-1:0(l(1,0)), m1) > 1

  BR / Ole

  1

Please sign in to leave a comment.