Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Date | Job No | Yesterday's Amount | Todays Amount | Variance |
---|---|---|---|---|
01/03/2016 | 1 | 11413 | 4757 | 6656 |
02/03/2016 | 2 | 6319 | 6319 | 0 |
03/03/2016 | 3 | 14887 | 4749 | 10138 |
04/03/2016 | 4 | 7529 | 7529 | 0 |
05/03/2016 | 5 | 11196 | 3734 | 7462 |
06/03/2016 | 6 | 2166 | 2166 | 0 |
07/03/2016 | 7 | 2117 | 920 | 1197 |
08/03/2016 | 8 | 2508 | 2508 | 0 |
09/03/2016 | 9 | 6854 | 6548 | 306 |
10/03/2016 | 10 | 14773 | 271 | 14502 |
11/03/2016 | 11 | 12304 | 9596 | 2708 |
12/03/2016 | 12 | 7192 | 7192 | 0 |
13/03/2016 | 13 | 829 | 1460 | -631 |
14/03/2016 | 14 | 3810 | 3810 | 0 |
15/03/2016 | 15 | 12268 | 564 | 11704 |
16/03/2016 | 16 | 12144 | 2310 | 9834 |
17/03/2016 | 17 | 606 | 606 | 0 |
18/03/2016 | 18 | 2464 | 2464 | 0 |
Any help would be appreciated.
how to I incorporate this into my set analysis which is used in the variance column
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)
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
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)
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))