topic Average calculation – should calculated by dynamic Date in QlikView

Average calculation – should calculated by dynamic Date

Sun, 24 Jun 2012 15:45:56 GMT

Hello all,

I have an expression that calculate the average

The dimension is selected from a variable

I like the average to be calculated taking the previous 7 days that was selected in the variable.

My current average calculation is this :

((sum(if(price='3.0', (rev*c)))/sum(aggr(if(price='3.0',(count(id))),c_date,price,c_hour)))*(Vcb_3))

So I guess I need to take “c_date,price,c_hour” and make them dynamic somehow.

Appreciate your help.

Tomer

Re: Average calculation – should calculated by dynamic Date

swuehl — Sun, 24 Jun 2012 16:36:21 GMT

Tomer,

not sure if I've understood. You are using this expression in a chart object, and your (only?) dimension is set using a variable?

Would be good if you could post a full definition of your dimensions (i.e. the variable) and expression (e.g. how Vcb_3 is defined). It would probably most easy if you could post a small sample app.

If you need to limit your expression to a certain date range (and this range might be defined by a selected date -7 days or variables), you could use set expressions with your aggregation functions, something like

sum({<Date = {">=$(vDate)-7<=$(vDate)"}>} if(price='3.0', (rev*c)))

(this is just an example, please look into set analysis in the HELP)

Regards,

Stefan

Re: Average calculation – should calculated by dynamic Date

Mon, 25 Jun 2012 06:14:50 GMT

Hi,

Yes it seems like “Set analysis” is what I’m looking for.

Thanks for that tip.

My dimension is a simple “DateFormat” that is taken from the user input.

Before making things complicated I I try to start with the basic:

When I try this:

sum({$<c_date = {'2012-06-22'} >} sales)

this one is working and ok, I’m happy with that

no I move to variable….

sum({$<c_date = {$('Vreference_Date') } > } sales)

my “Vreference_Date'” holds the same value as I added manually above (2012-06-22)

I try to remove the single quote, but the calculation ends up with ‘-‘ while with the single quote I got 0

If I change the c_date to a different dimension, like price and my formula look like this:

sum({$<c_price = {$(Vreference_Date) } > } sales)

then I got the right and my expected output, for the specific variable (price point) that I put.

Thanks for your help.

Tomer

Re: Average calculation – should calculated by dynamic Date

Mon, 25 Jun 2012 07:56:06 GMT

My single quotes location were misplaced,

Once that was fix I manage to get the result I’m looking for

The formula output looks like this:

sum({$<c_date = {'$(Vreference_Date)'} > } sales)

and I got the result of the selected date I put in the variable.

Now I need to take not only the date in the variable also to take the 7 days before and sum them all together.

Ideas?

Tomer

Re: Average calculation – should calculated by dynamic Date

swuehl — Mon, 25 Jun 2012 08:24:04 GMT

Using Dates in set analysis might get a bit tricky, it's not such easy to get the formats right the first time.

It is recommended that you are using numerical variables for the dates in set analysis, so if your variable definition now looks like

=today()

try using

=num(today())

[As you probably know, dates have a textual and numerical representation].

Having numerical date variables, I think your set expression should be very similar to the one I posted above

sum({<c_date = {">=($(vNumRefDate)-7)<=$(vNumRefDate)"}>} sales)

Hope this helps,

Stefan

Re: Average calculation – should calculated by dynamic Date

Mon, 25 Jun 2012 08:56:55 GMT

Yes this is helpful but I still have a problem limiting the selection

I have this now:

sum({<c_date = {'>=2012-06-22-7 <2012-06-22'}>}sales)

what actually is happening is that it sums all dates below 22nd

I need to limit it for the last 7 days and not the entire dates that I have.

Re: Average calculation – should calculated by dynamic Date

Mon, 25 Jun 2012 09:12:41 GMT

If you post a small example app we can easily take a look.

I think your problem is that this code won't work:

sum({<c_date = {'>=2012-06-22-7 <2012-06-22'}>}sales)

Swuehls code should do the trick; I can hardly add to his previous post: the date must FIRST be converted to numerical (so your 22-6-2012 will be a number like 41085 or something similar) and from that number you can extract the 7 days...

Roberto

Re: Average calculation – should calculated by dynamic Date

swuehl — Mon, 25 Jun 2012 09:18:09 GMT

Your dates are not using the numerical representation I suggested.

For example 2012-06-22 has a numerical representation of 41082

I think the problem is that 2012-06-22-7 won't be interpreted as date.

You can also try to create a second variable for your lower limit and subtract the 7 days in the variable definition.

Regards,

Stefan

P.S. For an advanced search in the set modifier, I would personally use double quotes instead of the single quotes.

Re: Average calculation – should calculated by dynamic Date

Mon, 25 Jun 2012 09:46:52 GMT

Thanks a lot for all your help.

At the end is did the following:

Once a specific date is put, I will calculate into different variables the MAX and MIN dates for calculation.

This will do the trick to me.

Appreciating all your help and support.

Tomer