Set Analysis - Year ago values

[odassier]

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

[sunny_talwar]

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

[odassier]

HI Anil,

Yes, I get a value for max date and it works fine each time I chose a different date in the selection box, but I think the selection box makis Qlik believe I only have one date in the data set and so any date minus a number returns just the value in the one selected date.

Olivier

[odassier]

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.

[sunny_talwar · Accepted solution]

What is the number you are expecting to see?

0.89%? Try this

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

[odassier]

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

[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)

[odassier]

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

[odassier]

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

[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

[odassier]

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