6 Replies Latest reply: Apr 22, 2016 10:58 AM by Gysbert Wassenaar

# Exclude rows based on set analysis value

I have a sample list of jobs with a spend value per job based on a previous days snapshot and today's snapshot. I need to exclude all rows of data which have 0 in the variance column

Yesterdays Amount uses the following Set analysis - sum({<rundate ={"\$(vPreviousRunDate)"}>} total_amount)

Today's Amount uses the following Set analysis - sum({<rundate ={"\$(vCurrentRunDate)"}>} total_amount)

Variance uses the following set analysis -  sum({<rundate ={"\$(vCurrentRunDate)"}>} total_amount) - sum({<rundate ={"\$(vPreviousRunDate)"}>} total_amount)

Booking DateJob NoYesterday's AmountTodays AmountVariance
01/03/201611141347576656
02/03/20162631963190
03/03/2016314887474910138
04/03/20164752975290
05/03/201651119637347462
06/03/20166216621660
07/03/2016721179201197
08/03/20168250825080
09/03/2016968546548306
10/03/2016101477327114502
11/03/2016111230495962708
12/03/201612719271920
13/03/2016138291460-631
14/03/201614381038100
15/03/2016151226856411704
16/03/2016161214423109834
17/03/2016176066060
18/03/201618246424640

Any help would be appreciated.

• ###### Re: Exclude rows based on set analysis value

how to I incorporate this into my set analysis which is used in the variance column

• ###### Re: Exclude rows based on set analysis value

Try using some helper variables:

Variables:

vAmountPrev: sum({<rundate ={'\$(vPreviousRunDate)'}>} total_amount)

vAmountCur: sum({<rundate ={'\$(vCurrentRunDate)'}>} total_amount)

vVariance: rangesum(\$(vAmountCur),-\$(vAmountPrev))

Chart expressions:

=sum({<rundate ={"\$(vPreviousRunDate)"}, [Job No]={"=\$(vVariance)<>0"}>} total_amount)

=sum({<rundate ={"\$(vCurrentRunDate)"}, [Job No]={"=\$(vVariance)<>0"}>} total_amount)

=\$(vVariance)

• ###### Re: Exclude rows based on set analysis value

I couldn't get your solution to work properly. the variables seem to have calculated the variance between the total data set. I need to compare the total amounts for the individual job between the 2 different rundates

• ###### Re: Exclude rows based on set analysis value

Like this?

Expressions:

1)

If(Sum({<rundate ={"\$(vCurrentRunDate)"}>} total_amount) - Sum({<rundate ={"\$(vPreviousRunDate)"}>} total_amount) <> 0,

Sum({<rundate ={"\$(vPreviousRunDate)"}>} total_amount))

2)

If(Sum({<rundate ={"\$(vCurrentRunDate)"}>} total_amount) - Sum({<rundate ={"\$(vPreviousRunDate)"}>} total_amount) <> 0,

Sum({<rundate ={"\$(vCurrentRunDate)"}>} total_amount))

3)

=sum({<rundate ={"\$(vCurrentRunDate)"}>} total_amount) - sum({<rundate ={"\$(vPreviousRunDate)"}>} total_amount)

• ###### Re: Exclude rows based on set analysis value

Don't put an = character in front of the expression in the variable if I didn't. And change the double quotes in the variables to single quotes:

Variables:

vAmountPrev: sum({<rundate ={'\$(vPreviousRunDate)'}>} total_amount)

vAmountCur: sum({<rundate ={'\$(vCurrentRunDate)'}>} total_amount)

vVariance: rangesum(\$(vAmountCur),-\$(vAmountPrev))