Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Cumulative and Rangesum() Color Conditioning

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

4 Replies
sinanozdemir
Valued Contributor III

Re: Cumulative and Rangesum() Color Conditioning

Hi Michael,

Maybe use RowNo(TOTAL)?

Not applicable

Re: Cumulative and Rangesum() Color Conditioning

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

Not applicable

Re: Cumulative and Rangesum() Color Conditioning

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

Not applicable

Re: Cumulative and Rangesum() Color Conditioning

Hi Michael,

You can use something like this :

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

Regards,

Vivek

Community Browser