topic RangeAvg - Above problem in QlikView

RangeAvg - Above problem

Thu, 23 Aug 2012 17:48:52 GMT

Hi Dear QV Community

I have a problem with an expression that has the following formula in a line graph from a Bar/Line Graph Object

(

IF (Año=Year(Today()) AND Mes=MONTH(Today())

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

)

)*90

If you check the graph the information is up to Aug 2012 for the bar graph, but the line graph corresponding to the formula above comes up to Oct 2012

Re: RangeAvg - Above problem

Thu, 23 Aug 2012 18:25:11 GMT

This is displaying correctly. The Rangeavg is taking 2 rows before the current and the current row. for sep you are getting the totals of Jul, Ago, and Sep.

Oct is giving you Ago,Sep, and Oct

Re: RangeAvg - Above problem

Thu, 23 Aug 2012 19:19:12 GMT

Ok marcsliving i know this but i need some solution over this issue

Re: RangeAvg - Above problem

Fri, 24 Aug 2012 12:12:24 GMT

How are your date fields linked in your data model? I am assuming that you can have months with no data linked correct?

One way could be to use an if statment to return null if the month is greater than month(today()) so something like this. You would then be able to check suppress nulls under dimension:

IF(MONTH(Yourdatefield)<=MONTH(TODAY()),

(

IF (Año=Year(Today()) AND Mes=MONTH(Today())

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

)

)*90)

Re: RangeAvg - Above problem

Fri, 24 Aug 2012 12:31:22 GMT

I used the formula

IF(MONTH(Yourdatefield)<=MONTH(TODAY()),

(

IF (Año=Year(Today()) AND Mes=MONTH(Today())

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

)

)*90)

and the QlikView is blocked (Not Responding).

And i use the calendar in FlagMatrix Method for this reason i use the Flag Base=1 that is a flag for selectons in time

Re: RangeAvg - Above problem

Fri, 24 Aug 2012 12:36:17 GMT

Try doing this on the script level.

IF((Yourdatefield)<=(TODAY()), Month(Yourdatefield)) as Month

This should make it so it will not pull any date greater than today for this month field.

Re: RangeAvg - Above problem

Fri, 24 Aug 2012 12:50:19 GMT

This option is not viable because I have records with dates greater than today

Re: RangeAvg - Above problem

Fri, 24 Aug 2012 13:06:27 GMT

Ok I guess I will try to understand your original formula a bit more.

From what I understand is that if the chart is on the current month and current year, use this formula:

RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

otherwise use this formula:

RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

What happens if you add one more part to that first IF statement?

(

IF (Año=Year(Today()) AND Mes=MONTH(Today())

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto),Año={$(=Year(Today()))},Mes={$(=MONTH(Today()))},Día={$(=if(getselectedcount(Día)>0,Día,DAY(today())))}>} [Valor total cartera]),0,3))

,IF(Año=Year(Today()) AND Mes<MONTH(Today())

,RangeAvg(Above(sum({<Base={1},$(vIgnoraProducto)>}[Valor total cartera]),0,3))

RangeSum(Above(sum({<Base = {1}>} [Valor ventas]),0,3))

)

)*90

Re: RangeAvg - Above problem

Fri, 24 Aug 2012 13:38:04 GMT

marcsliving

With this option works fine Thanks !!