topic Dimension depencence in expresions in QlikView

Dimension depencence in expresions

Mon, 02 Jul 2012 08:32:56 GMT

Hi,

I'm trying to do an expresion that calculates the cuantity of something in this month divided by the Average of the three last month for every month.

I put the month as dimension (Calendarmonth) and that expresion :

count(lines)/((count(<{Calendarmonth={ ">=$(=AddMonths(MonthStart(max(Calendardate)),-3))<=$(=AddMonths(monthend(Max(Calendardate)),-1))"}lines)/3).

The problem is that if I put this expresion in a text field, the results are the expeted ones, but when I put the expresion in graph, the results are not the expected cause the data I'm trying to count in the second part of the division is being affected by the dimension.

There is any way to avoid that?

Thanks.

Re: Dimension depencence in expresions

jjfabian — Mon, 02 Jul 2012 08:42:53 GMT

Hi,

you can ignore dimensions in set analysis by adding 'yourdimension='.

Re: Dimension depencence in expresions

Mon, 02 Jul 2012 08:45:50 GMT

Hi Jakob,

I cannot add it cause the dimension is CalendarMonth and I cannot set it to nothing cause it have to be the three last months.

Re: Dimension depencence in expresions

jjfabian — Mon, 02 Jul 2012 09:14:18 GMT

A solution would be to remove your dimension and add three separate expressions with a set analysis for each month - not pretty, but that would work 🙂

Re: Dimension depencence in expresions

Mon, 02 Jul 2012 09:26:42 GMT

I think I didn't explain well.

I need to have, for each month, the division between the actual value divided by the average of the three previous month, so I need the dimension.

For example:

Apr = 3

May = 5

Jun = 1

So the value of the expression in Jun will be : 1 / ((3+5+1)/3)

I hope you understand me

Re: Dimension depencence in expresions

vincent_ardiet — Mon, 02 Jul 2012 09:45:02 GMT

Hi,

Have you try to use the ABOVE function ?

Rangeavg(above(count(lines),1,3)) will return the average of your expression evaluated on the 3 previous months (if the month is your dimension).

Regards,

Vincent