Try to SUM one field minus another

Question & Answer
,
1

Hi Community,
I am trying to create a sum from taking the daily sum and subtracting it from the max threshold it can be. But QS is aggregating all items in the row and then subtracting ( I think). I just need it to subtract the current day’s data from the max threshold to see if I get a value that is greater than the max. Here are a couple examples of what I have tried to use so far:

sum({2X}-{usd_value}) - which gives me the first snip
sum(ifelse({metric_date}>=now(),{pac_total_inventory_value}+{cor_total_inventory_value}+{other_total_inventory_value}+{gft_total_inventory_value},0)) - second snip

Please help!

QS_SNip_1
QS_SNip_11183×391 42.2 KB

QS_SNip_2
QS_SNip_21179×385 39.3 KB

2

Hi @Jesse1,

I’ve tried to build a representative example using different data.
Using a PivotTable visual I added the following fields:

image
image842×192 9.69 KB

In my example, 2x Threshold is a calculated field as follows:

sum(Sales)-avg({2X})

This gives me a table as follows:

image
image934×363 34.2 KB

I believe the issues lies with multiple rows in the data and therefore you need to aggregate the 2X threshold using the avg() function.

Does this help with your solution?

Many Thanks,
Andrew

1 Like
3

This worked very well, now I am working to find a way to filter out anything that has a negative value so that only field items that breach the threshold are in the visual.

Secondly, avg works well for this since it has fairly consistent values, I wonder if it would work the same if the 2x values weren’t consistent.