topic Re: average age expression based on 2 dimensions in QlikView

average age expression based on 2 dimensions

Tue, 04 Feb 2014 18:47:40 GMT

I'm trying to duplicate a formula I use in Excel all the time, where I have a newer date (date sold) and an older date (date bought) to get to the age, for a group of lines. Since dates can all convert to numbers, I generally sum up each set of dates, subtracting the older date from the new. To get it into a month format, I take that difference and divide through by 30.4.

For instance, I think it should look something like

( Sum (DateSold) - (Sum (DateBought) ) / 30.4

I suspect it's the outside parentheses that are causing the problem, but so far I can't find the solution. Any ideas?

Re: average age expression based on 2 dimensions

Tue, 04 Feb 2014 19:04:29 GMT

Hi Barbara

If you really have to aggregate (and really want to calculate this using division by 30.4) I'd rather use Only instead of Sum. Still not sure though why your expression doesn't work. Does it return error or just NULL? Does it work without division by 30.4?

Lukasz

Re: average age expression based on 2 dimensions

Tue, 04 Feb 2014 19:24:00 GMT

Thanks - it came back as null with the expression I had written. I tried using Only instead of Sum and that got me closer, but it only showed the answer for some product lines and not across the entire portfolio. The answer appears to be the same using Avg as if I summed first and then divided. What worked was:

Avg (DateSold - DateBought)/30.4

ThanksLukasz Mastalerz

Re: average age expression based on 2 dimensions

maxgro — Tue, 04 Feb 2014 19:24:26 GMT

avg((DateSold- DateBought)) / 30.4