Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Data Sample:
Year | Sum (Qty 1) | Sum (Qty 2) |
---|---|---|
2016 | 1 | 2 |
2017 | 2 | 3 |
2018 | NULL | 4 |
Formula: Sum (Qty 1)/Sum(Qty 2)
Problem: I'm trying to create a line graph of Year vs [Sum( Qty 1) / Sum( Qty 2)]. The graph looks weird because it drops to 0 in 2018 due to the NULL value in Sum (Qty 1). Is there anyway to get the graph to not display year 2018 because Sum (Qty 1) is Null? How would you code that in set expression?
Any help would be greatly appreciated!!
Hi Jason,
You can also use : Sum({<Qty1={'*'}>}Qty1) / Sum({<Qty1={'*'}>}Qty2) in order to avoid Null values,
Hope it can help you with your issue,
Regards
Jon
May be this
Sum({<Year -= {2018}>}Qty 1)/Sum({<Year -= {2018}>}Qty 2)
Hey Sunny, thanks for the response. That was my first thought, but I was hoping there would be a way that I could avoid hardcoding a specific year to ignore
May be this
Sum({<Year = {"=Sum(Qty 1) > 0"}>}Qty 1)/Sum({<Year = {"=Sum(Qty 1) > 0"}>}Qty 2)
Hi Jason,
You can also use : Sum({<Qty1={'*'}>}Qty1) / Sum({<Qty1={'*'}>}Qty2) in order to avoid Null values,
Hope it can help you with your issue,
Regards
Jon
this worked for me, thanks a bunch!