RangeSum Expression Color based on Dimension Value

[Anonymous]

I have what I believe is a seemingly simple request from a user but I cannot seem to make it work.

I have a RangeSum totaling sales order dollars for the year. The users want to hide the section of the rolling total for months prior to current month. This way, we still get the entire aggregation of orders for the year  but we don't see the line for the earlier part of the year. The reason for this is because we want to overlay another graph on top of this with actual shipments that would fill in this whitespace.

I have tried to set the color based on the background color setting on the expression to be something like:

If(ORDER_DATE_MONTHYEAR<vCurrentMonth,white()) but it doesn't work. How do I make that section white?

[sunny_talwar]

To me it seems that it should work. Only thing I can think of is to make sure that both ORDER_DATE_MONTHYEAR and vCurrentMonth are date fields so that they can be compared.

Would you be able to share a sample?

[ramoncova06]

can you provide the range sum expresion ?

do you have any selection on the date ?

I use something like the following to do something similar to this (we do have at all times one selection for the Weekend)

if(only({1<WeekEnd=,ActualsWeekEnd=>}WeekEnd)<vWeek,rgb(228,228,228),rgb(0,0,0))

[Anonymous]

Here's the dimension:

=If(Year(ORDER_DATE_MONTHYEAR)=vCurrentYear,ORDER_DATE_MONTHYEAR)

We only want to see the current years rolling total

Here's the expression:

(RangeSum(Above(Sum({<Qtr=p(QUARTER),Year={$(vCurrentYear)},Month=p(MONTH),ITEM_VALUE_STREAM=p(VALUESTREAM)>} ORD_LINE_EXTAMOUNT),0,RowNo())))

The expression has some set analysis in order to use universal date filters.

[ramoncova06]

I agree with Sunindia, this might be something with the date format, make sure that both fields have the same format or qlikview might not be able to do the comparison

[Anonymous]

I have tried to do this by putting this in my formula and still no luck

If(MakeDate(Year(ORDER_DATE_MONTHYEAR),Month(ORDER_DATE_MONTHYEAR),1)<Date(vCurrentMonth),white())

Is it a problem with the Dimension being calculated to only show the current year?

[sunny_talwar]

Can you check if this gives you a number in text box:

1) Num(ORDER_DATE_MONTHYEAR)

2) Num(vCurrentMonth)

If this giving you a number, you will need to make sure that these two are not text strings.

HTH

Best,

Sunny

[ramoncova06]

what is the format for ORDER_DATE_MONTHYEAR and for vCurrentMonth ?

something like 'May-2015' ?

[Anonymous]

Yes - "May-2015"

[ramoncova06]

You need to use Date# for QV to recognize it as a date then you can use the makedate

=MAKEDATE(  YEAR(Date#(ORDER_DATE_MONTHYEAR 'MMM-YYYY')),Month(Date#(ORDER_DATE_MONTHYEAR ,'MMM-YYYY')))