29 Replies Latest reply: Aug 15, 2017 11:18 AM by Sunny Talwar RSS

    Set Analysis - Year ago values

    Olivier d'Assier

      Hello all,

       

      I've been stuck for days on a set analysis problem I hope you all can help me with.

       

      I have a set of portfolio returns for each month for the past several years and want to use set analysis expressions to capture the portfolio performance at various dates based on user input (via filters).

       

      For the latest date I've got:

       

      =only({$<date={"=$(=max(date))"}>} [Active Return])

       

      And that works fine.  If the user selects May 2017 for example, I get back the portfolio's annualized return as of that date.  Now for column two in this table I'd like to show the return one year prior to that selected date. 

       

      using: =only({<$(=max(date)-365)}>} Ann_Returns)

       

      doesn't work.  I may need a combined expression with $ for the max date (to take into consideration the user's selection for the date of the analysis) and a 1 for the year ago date so that the app does not think there is only one date in the data set (because of the selection).

       

      Any help would be appreciated,

       

      Olivier

        • Re: Set Analysis - Year ago values
          Sunny Talwar

          May be this

           

          =Only({<date = {"$(=Date(Max(date)-365, 'DateFieldFormatHere'))"}>} Ann_Returns)

            • Re: Set Analysis - Year ago values
              Olivier d'Assier

              HI Sunny,

               

              thanks for helping out.

               

              I tried your expression:

               

              =Only({<date = {"$(=Date(Max(date)-365, '[Date.autoCalendar.YearMonth]'))"}>} Ann_Returns)

               

              But it returns the same value as for the max date in column 1.  It may be because the date selection is turning my data into a single point in time and we may need to add a modifier to the expression to make it 'ignore' that fact?

               

              Olivier

                • Re: Set Analysis - Year ago values
                  Sunny Talwar

                  I wanted you to put the format within the single quotes, for instance if date is format as MM/DD/YYYY, then this

                   

                  =Only({<date = {"$(=Date(Max(date)-365, 'MM/DD/YYYY'))"}>} Ann_Returns)

                    • Re: Set Analysis - Year ago values
                      Olivier d'Assier

                      Sorry, my bad.

                       

                      Y tried with MM/DD/YYY in the expression and still get the same value as Max data in columns 1

                       

                      Olivier

                        • Re: Set Analysis - Year ago values
                          Sunny Talwar

                          Would you be able to share a sample to show the issue?

                            • Re: Set Analysis - Year ago values
                              Olivier d'Assier

                              Yes, sure, it's all dummy data for the moment so no problem sharing.  I'm also trying to use a Vizlib table extension in there, but if you just use the KPI in the box at the bottom, to test your expression, It should work in the table when I copy and paste it later.

                               

                              Here is the .qvf file.

                                • Re: Set Analysis - Year ago values
                                  Sunny Talwar

                                  What is the number you are expecting to see?

                                  0.89%? Try this

                                   

                                  =Avg({<Date = {"$(=AddYears(Max(Date), -1))"}>}Ann_Returns)

                                    • Re: Set Analysis - Year ago values
                                      Olivier d'Assier

                                      Hi Sunny,

                                       

                                      thanks for this, but I only get "-" in the second columns now.

                                       

                                      If you select Year = 2014, and Month = May, for example, you should get 3.03% for D-Yield, 1.85% for E-Yield, -0.72% for Profit, and -0.26% for Growth. 

                                       

                                      Instead I can only get the same values as column 1 or "-" so far.

                                       

                                      Olivier

                                        • Re: Set Analysis - Year ago values
                                          Sunny Talwar

                                          Your dates seems to be the last working date of the month... I suggest creating a new field in your data set

                                           

                                          LOAD Date,

                                             ....,

                                              Date(MonthStart(Date)) as MonthStartDate

                                          FROM ...

                                           

                                          and then this

                                           

                                          =Avg({<MonthStartDate = {"$(=MonthStart(Max(MonthStartDate), -12))"}>}Ann_Returns)

                                            • Re: Set Analysis - Year ago values
                                              Olivier d'Assier

                                              Thanks for this.

                                               

                                              Unfortunately, none of these work with the date filter turned on.  I think the filter makes this data set a single date data set and so no time series analytics is possible.  Once I remove the filter from the app, I get values, but in the case of your last expression, the average for the entire time period, with isn't what I'm after.  I want the value corresponding to that date since these are annualized returns as of each date.

                                               

                                              Imagine you had two columns of monthly data. Column one is month end dates, column two is your weight on those dates.  now you want to create an app to analyze that record and you want to be able to go to a certain month back in time and see what your weight was then, and compare it to now.  Did I lose weight compared to same time last year?  What about versus three years ago?  Do I  usually gain weight in the summers or around the Christmas holidays?  Which part of the year do I lose weight the most?  etc.  I'm trying to answer similar questions for portfolios so I knid of need to have the date filters turned on so the user can select the time frames they want to use for analysis.

                                               

                                              Cheers,

                                               

                                              Olivier

                                                • Re: Set Analysis - Year ago values
                                                  Sunny Talwar

                                                  I wonder what the issue might have been? One thing I missed is to ignore selection in Date field since MonthStartDate will be driven from Date field.... may be try this

                                                   

                                                  =Avg({<MonthStartDate = {"$(=MonthStart(Max(MonthStartDate), -12))"}, Date>}Ann_Returns)

                                                   

                                                  I know that you did find some solution, but if it is not the best solution, then I am ready to work with you to help you find something better. But if in case you are satisfied, then I guess we are good to go..

                                                   

                                                  Best,

                                                  Sunny

                                                    • Re: Set Analysis - Year ago values
                                                      Olivier d'Assier

                                                      HI Sunny,

                                                       

                                                      I'd still like to work on this as the expressions you gave me (with "Avg") return the average of the full time series rather than the value at a specific date, either Max(date) or the one selected as max date by the user using the filters.

                                                       

                                                      Also, as a side note (might need to start a new note for this one?), if my data has two columns, one for dates, one for values but I want to limit the line chart to show just the last 3 or 4 dates (i.e. 3 or 4 data points), what is the expression I can use in the Dimension Limitation to run on the measure?

                                                       

                                                      Cheers,

                                                       

                                                      Olivier

                                                        • Re: Set Analysis - Year ago values
                                                          Sunny Talwar

                                                          Before we do anything else, would you be able to reload the attached application and reattach it for me. I have added the MonthStartDate in the Summary table and it would be nice if we can work with this....

                                                            • Re: Set Analysis - Year ago values
                                                              Olivier d'Assier

                                                              ok, When I try to load data, it gives me an error message (error in the load script).  So the added field does not show up. 

                                                               

                                                              Just out of curiosity, since my data is month end, and there is an auto calendar in Qlik, why do we need to add that field?  Also, why do you use MonthStart in your expressions when my data is MonthEnd based?

                                                               

                                                              I'm a newbie so never used the Load Editor before and was not sure what you meant by "creating a new field in your data set", Sorry.


                                                              Right now, I get some strange behaviours.  The same expression in your app gives me the correct value (a single value at that date) but in mine, it gives me the average of all the dates for that portfolio.


                                                              Olivier

                                                                • Re: Set Analysis - Year ago values
                                                                  Sunny Talwar

                                                                  Although your data is monthend, but it seems like it is last working day of the month....

                                                                   

                                                                  Capture.PNG

                                                                   

                                                                  Now the problem is that when we try to go back 1 year from selected date... for instance when May 2016 (05/31/2016), it is looking for a date of 05/31/2015... but you don't have this date

                                                                   

                                                                  Capture.PNG

                                                                   

                                                                  You have 05/29/2015.... now I am suggesting you to use MonthStart which will give you dates like this

                                                                   

                                                                  05/01/2015

                                                                  06/01/2015

                                                                  07/01/2015

                                                                  .

                                                                  .

                                                                  .

                                                                  05/01/2016

                                                                  06/01/2016

                                                                  .

                                                                  .

                                                                  .

                                                                  and so on...

                                                                   

                                                                  Now when you go back 1 year from a start of the month, you will find a date which will have a value.... (you can use your own created MonthEnds, but I would rather suggest you to use MonthStart as MonthEnd is a timestamp and you will need to use Floor function to truncate the time portion of the timestamp)....

                                                                   

                                                                  Coming back to the error, what is the error message that you got when you tried running the attached file? May be post an image of the error message here

                                                                   

                                                                  Best,

                                                                  Sunny

                                                                    • Re: Set Analysis - Year ago values
                                                                      Olivier d'Assier

                                                                      Hi Sunny,

                                                                       

                                                                      I can save the app and open it in Qlik (labeled your “jp4 roadshow v3”).  If I go to the data manager it says it can’t show me the data since some parts (your MonthStart field) were created using the load editor and asks me to synch it with the load script data.  When I did that (and could see the new field column), then tried to Load Data, it gave me an error message that there was an error in the load script.  So I deleted the app, re-downloaded your attachment and this time went straight into the load editor and clicked on Load from there. It worked.  I now have your MonthStart field in the dataset (see attached).  I understand your point about working days, but if we use “AddMonths” instead of “AddYears” does that resolve the problem?

                                                                       

                                                                      Now I need to find the expression that works with the filters in the first sheet.  Alternatively, I can live with using the less visually appealing default filter boxes which let the user select multiple years at once.  Right now, your expressions with “Avg” at the start give me the average of the entire set of dates instead of just the one value for the selected date.

                                                                       

                                                                      Cheers,

                                                                       

                                                                      Olivier

                                                                      PS: I am based in Singapore (so 12 hrs ahead) and will be signing off soon, so don’t think I’m ignoring you if I don’t respond. Thanks for the help.

                                                      • Re: Set Analysis - Year ago values
                                                        Olivier d'Assier

                                                        Hi Sunny,

                                                         

                                                        I went back and tried a few more times, and your expression here seems to work if I add the filters after and make sure you can select multiple dates or a date range instead of a single date.

                                                         

                                                        Thank you,

                                                         

                                                        Olivier

                                          • Re: Set Analysis - Year ago values
                                            Petter Skjolden

                                            Your expression:

                                             

                                            =only({<$(=max(date)-365)}>} Ann_Returns)


                                            Lacks a left-hand-side fieldname - it have to be more like this:


                                            =only({$<date={"$(=max(date)-365)"}>} Ann_Returns)

                                            or

                                            =only({$<date={"$(=AddMonths(max(date),-12))"}>} Ann_Returns)


                                            • Re: Set Analysis - Year ago values
                                              Anil Babu

                                              Are you sure, Do you have value Ann_Returns for Max date?

                                              • Re: Set Analysis - Year ago values
                                                Sunny Talwar

                                                Check the attached now