Forum Discussion

Sivani's avatar
Sivani
Frequent Visitor
7 years ago

dynamic date labels

Hi,   Looking for help with figuring out how to make the week ending dates (in red at screenshot1 image) to change "Dynamically" every week without any manual changes. please see attached screensho...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI Sivani,

 

Can you please share some sample data to help us clarify your table structure to coding formula?

 

If your table contains both type(sales, units) and date column, you can try to write a calculated column with conditions based on 'today' function to add specific label. Then use it on matrix column field.


Sample column:

Dynamic =
VAR curr =
    TODAY ()
VAR rangeStart =
    curr - WEEKDAY ( curr, 1 )
        + 1
        - 21
VAR rangeEnd =
    curr
        + ( 7 - WEEKDAY ( curr, 1 ) )
VAR index =
    WEEKNUM ( curr, 1 ) - WEEKNUM ( [Date], 1 )
        + 1
RETURN
    IF (
        [Date] >= rangeStart
            && [Date] <= rangeEnd,
        [Type] & " Wk"
            & index
            & " "
            & [Date] - WEEKDAY ( [Date], 1 )
                + 1
    )

 

 

Regards,

Xiaoxin Sheng

  • Sivani's avatar
    Sivani
    Frequent Visitor
    7 years ago

    H Xiaoxin,

     

    Thank you for your reply. I am looking dynamic lables for attribute labels not for values/data.

     

    Ex: Sales WK1 attribute lable should display with date like Sales WK1- 8/18/2018 and that lable should updated dynamically for every week. I am using 24 attribues (like Sales WK1, Sales WK2, Sales Wk3, Units WK1, Units Wk2, Units Wk3, PM% WK1, PM% WK2, PM% WK3 etc... for showing 8 weeks rolling data) on my report.

     

    Please see already attached screenshot

     

     

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Sivani,

       

      My formula is dynamic based on today function(current system date), category labels will dynamic changes every week.(-21 means three week, eight week should be -56)

       

      You can also modify range start to display or calculate on more date range.

       

      BTW, if you mean dynamic modify excel data labels, you can post this to excel related forums to get more support.

       

      Regards,

      Xiaoxin Sheng