topic Exclude value in dimension using set analysis in App Development

Exclude value in dimension using set analysis

Anonymous — Thu, 26 Apr 2018 10:42:34 GMT

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!!

Re: Exclude value in dimension using set analysis

sunny_talwar — Thu, 26 Apr 2018 17:58:58 GMT

May be this

Sum({<Year -= {2018}>}Qty 1)/Sum({<Year -= {2018}>}Qty 2)

Re: Exclude value in dimension using set analysis

Anonymous — Thu, 03 May 2018 06:28:19 GMT

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

Re: Exclude value in dimension using set analysis

sunny_talwar — Thu, 03 May 2018 08:59:04 GMT

May be this

Sum({<Year = {"=Sum(Qty 1) > 0"}>}Qty 1)/Sum({<Year = {"=Sum(Qty 1) > 0"}>}Qty 2)

Re: Exclude value in dimension using set analysis

Anonymous — Thu, 03 May 2018 09:10:05 GMT

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

Re: Exclude value in dimension using set analysis

Anonymous — Fri, 04 May 2018 11:20:44 GMT

this worked for me, thanks a bunch!