topic Multiple gauges in a sheet for different time frames in QlikView

Multiple gauges in a sheet for different time frames

Thu, 16 Dec 2010 14:52:45 GMT

Hello all,

I am trying to present 3 or 4 guages based on either a selected date or a date from user input. I would like to present the order intake for the day, week, month and quarter as a percentage of the budget for each of those time frames. I have a master calendar linked to my data table but cannot determine how to write the dimension to select the time frames needed. I have attached my qvw file for review.

Any suggestions would be greatly appreciated.

Multiple gauges in a sheet for different time frames

Thu, 16 Dec 2010 15:59:38 GMT

You can do this based on a selection or an input variable. To use the variable, it would be something like:

(Sum({<BusDate={'>=$(=MonthStart(vEndDate))<=$(=MonthEnd(vEndDate))'}>}ORDACT))
/
(Sum({<BusDate={'>=$(=MonthStart(vEndDate))<=$(=MonthEnd(vEndDate))'}>}ORDFOR))

That would be for the month of the date entered as input date. The other spans wouldn't be too much more difficult, but it depends on how you define week and quarter.

Based on a selection would be a little different. When using selections, you have to consider how your user is going to make the selection. You probably need the user to select only one date. Then you would make the formula based off that selection using Concat() or GetFieldSelections() or P().

Multiple gauges in a sheet for different time frames

Mon, 20 Dec 2010 01:53:49 GMT

Thank you for your response. I am not sure I stated my desire in enough detail. I have attached my qlikview space with some additional detail.

The first guage shows the orders by date per the current selections. The second guage should show the order for the week of the current selection date (in the case shown the week of 11/7/10). It should be the sum of the orders for days 11/1 to 11/7.

I have tried to enter the set analysis formula below for the Weekly Orders Expression:

(Sum({1<Bus_WeekEnd={BusDate}>}ORDACT))/
(Sum({1<Bus_WeekEnd={BusDate}>}ORDFOR))

and the result is too few expressions. I am sure I have not entered the expression correctly, but not sure what is missing. Again any assistance is appreciated.

Multiple gauges in a sheet for different time frames

Mon, 20 Dec 2010 21:58:52 GMT

Your expression is invalid. It is returning null, which is why you are getting too few expressions.

You should build your Set Analysis expressions in a straight table. It will make it easier to verify that your expression is working.

In your document, the only valid BusDate is 11/4/2010. You don't have any records with a Bus_WeekEnd = 11/4/2010 (since it isn't a week end date). Therefore, nothing is being returned.

You could try this:

(Sum({1<Bus_WeekEnd={'$(=Only(Bus_WeekEnd))'}>}ORDACT))/
(Sum({1<Bus_WeekEnd={'$(=Only(Bus_WeekEnd))'}>}ORDFOR))

That will take the selection and as long as there is only one Bus_WeekEnd to return, it will count everything in that week.

Multiple gauges in a sheet for different time frames

Tue, 21 Dec 2010 00:43:06 GMT

Thank you very much. The formula works perfectly.