Still new with Qlikview, and Set Analysis is really taking some time to get used to. I've read through quite a few posts about looking backwards for dates, but none of them have helped. I have to create text boxes in a bar graph that will hover over each column. The text box should show the total amount of the stacked column. The dimension for each column is a month. The last column is no problem as that's just the maximum month in the table, but I can't get a box to work for the previous month. I've tried the addmonths function, but I'm obviously failing to write the statement correctly.
I'm guessing based on what I've read that it should look something like this:
This has been driving me crazy for a while now, so I'd greatly appreciate it if one of you could help wipe away some of my ignorance about how to properly write this statement.
You must specify how many months to look backwards in your addmonths expression. Right now, all you are doing is specifying the date to be based on (which is Max(ReportDate) minus 10 days), but the function is returning null.
If you want to look for 1 month back, write something like:
AddMonths(max(ReportDate,'YYYYMM')-10, -1)
Though I dont know if that "-10" should be there (Do you want to substract 10 days from the max Date?).
I would recommend to work with this expression in a textbox first to get it to work, before using it in a set analysis expression. Also, I dont know how is it that your are placing this text boxes in the chart. Are you using the Text in Chart option in the presentation tab?. If so, the expression will not be evaluated relative to each bar. Or maybe you are creating a different expression for each textbox related to each month. Is that why there is a "-10"? What you mean by that is to look back 10 months?, then all you need is to place a comma before "-10" and forget about the other "-1" I mentioned before.
I would like to recommend using a new expression that will show the desire value for the previous month ONLY in the pop up label (not as a new bar), instead of creating a separate text box for each month. You do that by checking the box 'Text as Pop Up' in the Expressions Tab, and unchecking all the others for that new expression.
Thank you for the help. I can't use the pop-ups since we have to export this chart for a report, so I need to show the totals in boxes. I'm using the Text in Chart boxes over each column, with each box having a different look-back number. In the case of my example, it should look back 10 months. I did as you mentioned in your post and tried just the date formula in the text box and it worked perfectly as:
Hi Jon, sometimes set analysis expressions are affected by indirect selections, in this case, I'm guessing you might have list boxes for month and year so the user can select them. As this list boxes are indirect selections to the field ReportDate, you have to clear them within the set analysis.
I was using the set analysis with the selections cleared, although you do make a good point about clearing those, so I could incorporate that into the analysis. I'm really interested to see how this can be solved. I think this is the toughest issue I've come across so far.
Hi Jon, there are a number of possibilities that can go wrong with set analysis, probably the best way we can help you is posting a qvw that briefs your issue.
My last guessing, without sample data, is that when you apply max(ReportDate) returns an integer and the function addmonths takes a date as an argument, so you might have to apply an extra date function, like this:
