12 Replies Latest reply on May 25, 2020 4:22 AM by Mahfooj Khan

    Text table of latest date?

    Vik Wu

      Post image

      Is there anyway to create a text table like this? Column 1 is always yesterday and then day before yesterday and more. I tried this

       

      [Report Date] = {MAX([Report Date])}

       

      but it return True/False and I can't exclude False since Y-1 and Y-2 is false. Also I have no idea make a calculated field of % change with this. Any idea?

        • 1. Re: Text table of latest date?
          Shweta Jain

          Hello Vik,

           

          You can use this calculation to filter your dates for last three days only.

           

          Date Filter: DATEDIFF('day',[Order Date],{MAX([Order Date])}) <=2

           

          Drag "Date Filter" to filter shelf with "True" selected. Then sort the date on your view in descending order to make the last date appear first.

           

          For Percentage difference, you need to use the quick table calculation.

           

          Attached is the workbook for your reference.

           

          If you have different query, request you to attach a sample workbook with dummy data.

           

          If this solves your query, please mark my post as correct.

           

          Thanks,

          Shweta Jain

          • 2. Re: Text table of latest date?
            Vik Wu

            Hi Shweta,

             

            The percentage must into the same cell with value of metric, that's why #### (%##) in my screenshot. So I guess this percentage must be calculated field right? Then how to create it?

            • 3. Re: Text table of latest date?
              Shweta Jain

              Hello again,

               

              Thanks for explaining it one more time. If I follow you correctly, you are looking for something like this:

              To achieve this, you need to write 3 calculations: Table Calc for Percentage difference, Open bracket & Close bracket.

               

              Put all these on text on Marks Card

              And at last, arrange these calculations in text window as below:

               

              Please confirm if this is what you are looking for.

               

              Thanks,

              Shweta Jain

              • 4. Re: Text table of latest date?
                Vik Wu

                Hi Shweta,

                 

                Please help me with 2 things:

                - Explain your Percentage difference calculation (LOOKUP and ABS meaning)

                - For comparison, the latest day will be based, and compare to previous days, for example: Standard class 30/12 is 23, then 28/12 will be 52(-55.8%)

                 

                Also, in my example table screenshot, it's not just 1 measure 'Quatity' but every row of metric is 1 measure. For example: first cell of row 2 is Metric A name, then Metric A value for each day, repeat with row 3 Metric B. Hope you understand.

                 

                Thanks.

                • 5. Re: Text table of latest date?
                  Shweta Jain

                  Hi Vik,

                   

                  Request you to attach a sample workbook with the desired requirement.

                   

                  Thanks.

                  • 6. Re: Text table of latest date?
                    Vik Wu

                    Hi Shweta,

                     

                    I attached demo file. So what I want is Metric A May 20 cell will be 49 (-55.1%). Same with others. Hope you understand. Thanks.

                    • 7. Re: Text table of latest date?
                      Mahfooj Khan

                      Hi,

                       

                      Is that what you were looking for?

                      Workbook attached for your reference. Let us know if this help.

                       

                      Mahfooj

                      • 8. Re: Text table of latest date?
                        Vik Wu

                        Hi Mahfooj,

                         

                        How do you move Report Date to top with just 1 pill of Report Date? Another problem is I need to fix the latest date value as base to compare to D-1, D-2, or more. Is there any way to do it?

                        • 9. Re: Text table of latest date?
                          Mahfooj Khan

                          Hi,

                           

                          To move the date headers at top, goto Analysis menu tab>Table Layout>Advanced then uncheck the below option.

                          To fix the date you mean whatever maximum date in your source from that you wants to compare Previous and Day before yesterday values. If so then follow the below approach.

                          Correct me if I'm wrong.

                           

                          Mahfooj

                          • 10. Re: Text table of latest date?
                            Vik Wu

                            Hi Mahfooj,

                             

                            To filter last 3 day we can use relative date then put it in the filter, why use calculation field?

                            Another thing is about % change, metric A value May 19 is 22, then I need May 21 is 68 (-67.6%), I need to compare all to the first column, not previous column. Is there anyway to do it?

                            • 11. Re: Text table of latest date?
                              Vik Wu

                              Also, is there a way to rotate the axis label?

                              And color % change, red for negative, green for positive?

                              • 12. Re: Text table of latest date?
                                Mahfooj Khan

                                Hi,

                                 

                                1. Yes, you can use relative date filter to show last 3 days. I've used LOD{} calculation because in your sample data source you've data till 21st may. In that case relative date filter will not show relevant information.

                                2. % Change calculation has been modified.

                                 

                                3. You can rotate the label like this

                                4. You can format the label +ve % and -ve% values green and red by creating another set of labels for each metrics. Find the calculation in attached workbook.

                                 

                                Workbook attached for your reference. Hope this will work.

                                 

                                Mahfooj