topic Re: RangeSum Expression Color based on Dimension Value in QlikView

RangeSum Expression Color based on Dimension Value

Anonymous — Tue, 26 May 2015 17:32:41 GMT

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?

Re: RangeSum Expression Color based on Dimension Value

sunny_talwar — Tue, 26 May 2015 17:40:30 GMT

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?

Re: RangeSum Expression Color based on Dimension Value

ramoncova06 — Tue, 26 May 2015 17:42:12 GMT

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))

Re: RangeSum Expression Color based on Dimension Value

Anonymous — Tue, 26 May 2015 18:37:30 GMT

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.

Re: RangeSum Expression Color based on Dimension Value

ramoncova06 — Tue, 26 May 2015 20:30:45 GMT

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

Re: RangeSum Expression Color based on Dimension Value

Anonymous — Tue, 26 May 2015 20:33:42 GMT

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?

Re: RangeSum Expression Color based on Dimension Value

sunny_talwar — Tue, 26 May 2015 20:35:35 GMT

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

Re: RangeSum Expression Color based on Dimension Value

ramoncova06 — Tue, 26 May 2015 21:01:35 GMT

what is the format for ORDER_DATE_MONTHYEAR and for vCurrentMonth ?

something like 'May-2015' ?

Re: RangeSum Expression Color based on Dimension Value

Anonymous — Tue, 26 May 2015 21:07:59 GMT

Yes - "May-2015"

Re: RangeSum Expression Color based on Dimension Value

ramoncova06 — Tue, 26 May 2015 21:12:11 GMT

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')))

Re: RangeSum Expression Color based on Dimension Value

Anonymous — Tue, 26 May 2015 21:13:22 GMT

Hi Sunny --

I've tried both all of these and none work:

If(num(ORDER_DATE_MONTHYEAR)<num(today()),white())

If(num(ORDER_DATE_MONTHYEAR)<=num(date(monthstart(today()), 'MMM-YYYY')),white())

If(only({1}num(ORDER_DATE_MONTHYEAR))<=num(date(monthstart(today()), 'MMM-YYYY')),white())

Re: RangeSum Expression Color based on Dimension Value

ramoncova06 — Tue, 26 May 2015 21:15:59 GMT

Though the makedate is not needed with Date# Should be enough, this will give you the same result that my previous post

Date(Date#('May-2015','MMM-YYYY'))

Re: RangeSum Expression Color based on Dimension Value

sunny_talwar — Tue, 26 May 2015 21:17:24 GMT

You just need to check the two in a text object if they are Numbers or text before you use any of the ifs you just mentioned above.

Sent from my iPhone

Re: RangeSum Expression Color based on Dimension Value

Anonymous — Tue, 26 May 2015 21:18:00 GMT

I've tried the following and still not working.

If(MAKEDATE(YEAR(Date#(ORDER_DATE_MONTHYEAR, 'MMM-YYYY')),(Month(Date#(ORDER_DATE_MONTHYEAR ,'MMM-YYYY'))),1)<Date(today()),white())

If(Date(Date#(ORDER_DATE_MONTHYEAR,'MMM-YYYY'))<Date(Today()),white())

Re: RangeSum Expression Color based on Dimension Value

Anonymous — Tue, 26 May 2015 21:19:56 GMT

I checked them. The numbers are different - but it does not work in the color.

However, I did notice that I only got a number in the text box when I had only 1 month selected.

Could it be a similar problem?

Re: RangeSum Expression Color based on Dimension Value

ramoncova06 — Wed, 27 May 2015 00:26:52 GMT

I believe it might be a bug with QV and the graph style, since you only leave a line it does disappear

Re: RangeSum Expression Color based on Dimension Value

Anonymous — Wed, 27 May 2015 12:40:45 GMT

Hi Ramon --

Here's a copy of the section on my app with this chart. Can you take a look? It's just a filled line chart.

Thanks!

Re: RangeSum Expression Color based on Dimension Value

ramoncova06 — Wed, 27 May 2015 19:50:16 GMT

The issue I see is what that specific type of graph, bar chart works fine