8 Replies Latest reply: Jun 28, 2018 11:47 AM by Sunny Talwar RSS

    Max value from a range of dates

    Lee Mychajluk

      I have data provided in the following format:

       

      Asset_Date | YTD_ANA | Rev

      1/1/2018 | 100 | 10

      2/12018 | 101 | 9

      3/1/2018 | 99 | 10

       

      The Rev (Revenue) column can be summed over a period, but the ANA value is for a point in time.

       

      I have a Pivot table that has a Fiscal Year as a Dimension (based on the Date) in a column. I use this formula (stored in a variable) to get the YTD ANA value:

      (Sum({$<Asset_FiscalMonth={"$(=vMaxFiscalMonth)"}>}[YTD_ANA]))

       

      Asset_FiscalMonth is an Integer calculated in the Load Script.

      vMaxFiscalMonth is the last fiscal month from the data, also calculated during load.

       

      If I add a second dimension for Asset_FQtr to the columns, I create a 'drill down' in the pivot table that is desirable. But, the YTD ANA formula doesn't work - I get 0 in that column when the quarter is expanded / visible. I think I understand why, in that I need to get the ANA from the last month in the selection, and not the vMaxFiscalMonth, so I change the formula to:

      Sum({$<Asset_FiscalMonth={"$(=Max(Asset_FiscalMonth))"}>}[YTD ANA])

       

      Basically, I'm looking for the YTD_ANA value from the last month respective of the dimension. But, I can't seem to get that formula to return a value. In the Q1 column, it should return the value of '99' from my sample data above.

       

      I'm assuming the same formula would allow me to add the Month to the drill down and still work, so I can drill down through YTD, QTD, and MTD values.

       

      Anyone see a problem w/ the formula?

      Sum({$<Asset_FiscalMonth={"$(=Max(Asset_FiscalMonth))"}>}[YTD ANA])

       

      Thanks for any insights!

        • Re: Max value from a range of dates
          Sunny Talwar

          How about this

           

          FirstSortedValue([YTD ANA]), -Asset_FiscalMonth)

            • Re: Max value from a range of dates
              Lee Mychajluk

              Sorry... I should've mentioned that there is other data in the table. I can sum the ANA for the records within a given Month, but not across months, so I need the 'Sum' function here. Thanks!

                • Re: Max value from a range of dates
                  Sunny Talwar

                  May be this

                   

                  FirstSortedValue(Aggr(Sum([YTD ANA]), Asset_FiscalMonth), -Asset_FiscalMonth)

                    • Re: Max value from a range of dates
                      Lee Mychajluk

                      I tried your formula w/o success.

                       

                      Any reason the method I was using (w/ the Set) wouldn't be valid? My original formula seemed to work fine. I think I  just need to substitute in the 'last' available fiscal month to make it work regardless of the date dimension used.

                        • Re: Max value from a range of dates
                          Sunny Talwar

                          From what I understand... Max(Asset_FiscalMonth) differs based on the Asset_FQtr. and you would want to see the Sum only for the max fiscalmonth for each quarter, right?

                            • Re: Max value from a range of dates
                              Lee Mychajluk

                              Not quite. ANA is an average (Average Net Assets) of a holding, and is provided to me as a YTD value for each month on a per holding basis. The ANA value changes for each holding, and I have the ANA for each month. Revenue is another value that I have per holding. I can sum Revenue across months, since the value is for a given month (not as a Running Total), but if I need the YTD ANA, it should be from the most current Month in a selection.

                               

                              Ex.. If I wants to calculate FYTD numbers for the first quarter, in my app I would select FQ1 and the Sum formula would be the Sum where the FiscalMonths are in 1-3. But, if I wanted FYTD ANA, I would need to only use the ANA numbers from the 3rd month (the max month in the selection).

                               

                              Hope that makes sense....

                    • Re: Max value from a range of dates
                      Lee Mychajluk

                      Sunny,

                       

                      I was looking at your replies in this thread - How to make dimensions in chart works for function in set analysis

                       

                      Your formula:

                      =Sum({<Lookup_Num={"$(='<=' & (Max(Lookup_Num)-1))"}>}Sales)

                       

                      ..is very similar to what I want to do, but instead of 'Lookup_Num' I'm using a Month value. If I substitute in my field names, I get this formula:

                      Sum({<Asset_FiscalMonth={"$(='<=' & (Max(Asset_FiscalMonth)))"}>}[YTD ANA])

                      (I also took out the -1, as I don't think I need it in my case)

                       

                      In reviewing with a colleague, we think the problem is that the set gets evaluated globally before the Sum, thus returning the wrong Max(Fiscal_Month) value to the Sum expression. I don't seem to get any value back, and I'm not sure how to evaluate that within the dimension.

                       

                      Any other thoughts? Thanks!

                        • Re: Max value from a range of dates
                          Sunny Talwar

                          In reviewing with a colleague, we think the problem is that the set gets evaluated globally before the Sum, thus returning the wrong Max(Fiscal_Month) value to the Sum expression. I don't seem to get any value back, and I'm not sure how to evaluate that within the dimension.

                          Yes and this is exactly why I propose FirstSortedValue() or Aggr() instead of set analysis