Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My table has two values, either $1,000 or $0 for each month. I would like every month to show the same value ($1,000) through expression.
September $1,000
October $1,000
November $0
December $0
January $1,000
How can I get November and December to also show $1,000.
Thanks in advance!
Thanks Chanty4u and Vegar for your suggestion!
The amounts will be different for different accounts, so I would like an expression that would change accordingly.
I was able to do a max on an aggregate by using the aggr function to aggregate on the month.
max(TOTAL aggr(sum(distinct Amount), Month))
can you try this?
test:
LOAD * INLINE [
Month,amount
September, "$1,000"
October, "$1,000"
November, $0
December, $0
January, "$1,000"
];
result:
load * ,
if(amount='$0','$1,000',amount) as New Resident test;
DROP Table test;
exit SCRIPT;
What is the logic? Is it a static $1000 you want? If so you could just write $1000 in your expression rather than using the amount field.
Or so you have situations where you do want to present $0?
Another expression that could help without any needs for script edits is:
=max(TOTAL [Amount field])
Thanks Chanty4u and Vegar for your suggestion!
The amounts will be different for different accounts, so I would like an expression that would change accordingly.
I was able to do a max on an aggregate by using the aggr function to aggregate on the month.
max(TOTAL aggr(sum(distinct Amount), Month))