we have an official requirement as to the layout of diagrams in QlikView: There should be a diagram - a barchart or else - for the current evolution of values - that's not the problem.
Next to that, there should be two historical values (e.g. prior_month and same_month_prior_year). That cannot be a diagram as the values are completely distinct. For now, I use textboxes to display those - one as background and two on top of it with the values. And here comes the challenge:
The values I use come from a list of damage_events. As one of the two historical values, I would like to display the (individual, not cumulative) amount of damages we had in the "worst" month of this year, that is, the amount in the month with the highest amount. I know part of how to find that out - with set_analysis and the RANK() fct. - but beforehand, I'd need the total of damages for every month.
In QlikView, afaIk, I cannot "stack" several formulas "one on top of another", only in the script and display areas. Thus the only way to do that would be to add a separate LOAD statement for (the cumulative damage_value of) every month UP TO NOW - there is the problem: I guess I will have to write a LOOP in my script, looping through every month up to the one prior to the current month and repeat the same LOAD statement every time, adapting the WHERE clause every time.
I don't have a lot of experience with LOOPs. Can anybody help me there, please?
I have tried out a static LOAD statement for Jan - that works and once I have values for Jan to (May), I can find out when the amount was highest and display that.
Re: Displaying historical values next to a diagram
no, it won't work that way. I guess I need to generate another table by way of a RESIDENT, using the AGGR fct to load only one record per month and the sum of damage_amounts for that month. Then I can apply a RANK fct and find out which month had the highest amount.