RangeAvg - Above problem

[#1]

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

[#2]

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

[#3]

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

[#4]

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)

[#5]

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

[#6]

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.

[#7]

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

[#8 · Accepted solution]

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

[#9]

marcsliving

With this option works fine Thanks !!