topic Re: Cumulative and Rangesum() Color Conditioning in QlikView

Cumulative and Rangesum() Color Conditioning

Tue, 11 Aug 2015 12:48:48 GMT

Using data that is reported on a month to month basis, I am trying to color condition the data based on the following:

=
IF(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]), vColorFORECAST,

IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Act/Fcst])
-
Sum({1<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Plan])) < 0, vColorACTUALRED,

IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Act/Fcst])
-
Sum({1<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Plan])) >= 0, vColorACTUALGREEN)))

However, my color conditions are only comparing each months Act/Fcst less Plan, as opposed to the cumulative value up to the date in question. I have since tried the following, but have yet to format the colors correctly. any and all help is much appreciated. Best.

=
IF(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]), vColorFORECAST,

IF(
Rangesum(Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Act/Fcst]), 0, RowNo()))
-
Rangesum(Above(Sum({1<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Plan])), 0, RowNo()) < 0, vColorACTUALRED,

IF(
Rangesum(Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Act/Fcst]), 0, RowNo()))
-
Rangesum(Above(Sum({1<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Plan])), 0, RowNo()) >= 0, vColorACTUALGREEN)))

Re: Cumulative and Rangesum() Color Conditioning

sinanozdemir — Tue, 11 Aug 2015 12:57:45 GMT

Hi Michael,

Maybe use RowNo(TOTAL)?

Re: Cumulative and Rangesum() Color Conditioning

Tue, 11 Aug 2015 13:07:29 GMT

Hi,

Please try Above only in instead of Rangesum, and use the label of the field that you have given for the expression in the chart :

Like if expression Label is "Label" then your expression for cumulative sum will be like.

= Above([Label]) + Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst])

Hope This Helps.

Regards,

Vivek

Re: Cumulative and Rangesum() Color Conditioning

Tue, 11 Aug 2015 13:13:50 GMT

Great, but how do you account for the first value? Using the following formula I was able to color condition my data, but am missing the first value.

Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]))

Re: Cumulative and Rangesum() Color Conditioning

Tue, 11 Aug 2015 16:09:38 GMT

Hi Michael,

You can use something like this :

if(isnull(Above([Label])), 0, Above([Label]) + "Your Expression ")

Regards,

Vivek