Qlikview help needed in using aggregate functions in dimensions

[#1]

Hello all,

I have a scenario and i want you to help me here.

We have a calendar where the user can select two dates. between those two dates, we want to calculate the daily, weekly and monthrly space growth.

For example

The user select Date D1, and Date D2. then there is another dimension (Databases). Then for each database, the space at minimum date and maximum dates should be calculated between dates D1 and D2. then i want to take the difference of space at Max(date) and space at min(date)

Table

----------

Date

Database

used space

How can i make the above expression in a chart? i mean how can I take the Total(Used space) at maximum(date) then total(used space) at minimum(date) and then take the difference of these two spaces.

I cannot do this in the load script, since the max and min are between the dates selected by the user, so this is calculated at run time

Please ask if unclear

-br

Arif

[#2]

What you need to do is use variables for D1 and D2 instead of field selections.

Just go into the variable overview and add a variable called vMinDate, write anything in there, and do the same for vMaxDate.

Then make an input box with box vMinDate and vMaxDate and use the following code:

Then do the following:

=sum({$<[Date]={"$(vMinDate)"}>} [Used space]) - sum({$<[Date]={"$(vMaxDate)"}>} [Used space])

I would probably just put it in a text box and write something like...

='Used Space Between: ' & vMinDate & ' and ' & vMaxDate & ' = ' & sum({$<[Date]={"$(vMinDate)"}>} [Used space]) - sum({$<[Date]={"$(vMaxDate)"}>} [Used space])

[#3]

Hello,

Thanks for your reply. I have a few more questions

1) In input box, how do we select the min and max date. I have already selected the variables for mindate and maxdate, but now i dont know how do these variables related to the date field in my table. In my input box, there is the variable name is showing on the left, but there is another column on the right, which is empty. I am just a bit confused how does the user give the two dates in which these calculations need to be made. How does the user select dates here?

2) The expressions that you have provided, do we need to use it in our data table, where we need to show the difference?

Thanks

Arif

[#4]

1) Just add an input box with Date1 and Date2 as the variables. When the user types in a date, the variable will take that value. If you look in the input box properties you can make it a drop down so they just select the value for the variables, or you just type them into the blank space.

2) I'm not exactly sure what you mean, but here's an example that might answer your question.

Let's say you type 1/1/2010 into the input box for Date1 and 3/6/2010 for Date2. The expression I gave will take the used space for 1/1/2010 and then subtract the used space for 3/6/2010. I think I did that backwords so you should probably put Date2 before Date1.

After reading your question closer I'm not 100% sure this is what you want because you said "between" those dates. So in my example above, what exactly are you looking for? Just the total used space between 1/1/2010 and 3/6/2010? The used space on 3/6/2010 minus the used space on 1/1/2010?

[#5]

Hello,

Thanks for your reply. This is exactly what we want. The difference of space between these two dates. however, for user to be entering the date manually looks a bit inconvenient. Is it possible that we can somehow related this to a calendar or something so that user can select the date rather than entering

,br

Arif.

[#6]

Yeah that can be a pain especially b/c the space to click on is ridiculously small. You can try sliders, using the drop down (which is also really hard to click on), or making buttons if you know there are certain days that they are more likely to want to see. As far as making a pretty list box type of thing.. I'm not exactly sure how to do that off the top of my head but I imagine it can be done.