Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
odassier
Creator II
Creator II

Set Analysis - Year ago values

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

29 Replies
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....

odassier
Creator II
Creator II
Author

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

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

odassier
Creator II
Creator II
Author

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.

sunny_talwar

I understand your point about working days, but if we use “AddMonths” instead of “AddYears” does that resolve the problem?

It won't...

Looking at the new reloaded app now...

sunny_talwar

Check the attached now

odassier
Creator II
Creator II
Author

Great, the expression seems to work well no matter what date I select in the date selection filters. Thanks a lot.

Now one more favour. During my day I did work on other parts of the app and added data so I now have new sheets with other visualizations etc. How do I add the MonthStart from the load scrip now to be able to reuse your expression? Sorry, never touched the data load script before and it looks tricky. IN your app the data is now “Generated by Script” and I can’t edit the tables anymore using the Data Manager. Any way around that?

Olivier

sunny_talwar

I am very new to Qlik Sense as well and don't really understand the new functionalities which were not there in QlikView. This probably would be a question for a person who has been using Qlik Sense for a while.

odassier
Creator II
Creator II
Author

No worries, thanks for the help.

I’ll try to add a MonthStart field directly in my table using the datamanager. I see you can add a calculated field, and there is a function “MonthStart()”. Now I just need to figure out how to make it work.

Thanks for your help with the expression, I could not have done it without you.

Olivier

sunny_talwar

No problem at all... I am glad we were able to help...

Best,

Sunny