simplified set analysis + if expression

[nareshthavidishetty]

Hi,

I have used the below logic and it is working fine.But the query time for the logic is more.

Can i get an simplified logic:

Logic:

if(isnull(num(Sum({$<[Month Abbr]=, Year=,[Month Year]=, [Quarter Name]=, [Calendar Date]={"$(=date(monthend(AddMonths(floor(Max([Calendar Date])),-6)),'DD-MMM-YYYY'))"}>}[# QN Open]),'#,##0')),null(),

if( num(sum({$<[Month Abbr]=, Year=, [Month Year]=, [Quarter Name]=, [Calendar Date]={"$(=date(monthend(AddMonths(floor(Max([Calendar Date])),-6)),'DD-MMM-YYYY'))"}, [QN Age 30+ Days]={1}>} [# QN Open]),'#,##0') /
num(sum({$<[Month Abbr]=, Year=, [Month Year]=, [Quarter Name]=,[Calendar Date]={"$(=date(monthend(AddMonths(floor(Max([Calendar Date])),-6)),'DD-MMM-YYYY'))"}>}[# QN Open]),'#,##0')*100 > 25,


Num( num(sum({$<[Month Abbr]=, Year=, [Month Year]=, [Quarter Name]=, [Calendar Date]={"$(=date(monthend(AddMonths(floor(Max([Calendar Date])),-6)),'DD-MMM-YYYY'))"}, [QN Age 30+ Days]={1}>} [# QN Open]),'#,##0') /
num(sum({$<[Month Abbr]=, Year=, [Month Year]=, [Quarter Name]=,[Calendar Date]={"$(=date(monthend(AddMonths(floor(Max([Calendar Date])),-6)),'DD-MMM-YYYY'))"}>}[# QN Open]),'#,##0')*100,'#,##0.0%       ■'),

Num( num(sum({$<[Month Abbr]=, Year=, [Month Year]=, [Quarter Name]=, [Calendar Date]={"$(=date(monthend(AddMonths(floor(Max([Calendar Date])),-6)),'DD-MMM-YYYY'))"}, [QN Age 30+ Days]={1}>} [# QN Open]),'#,##0') /
num(sum({$<[Month Abbr]=, Year=, [Month Year]=, [Quarter Name]=,[Calendar Date]={"$(=date(monthend(AddMonths(floor(Max([Calendar Date])),-6)),'DD-MMM-YYYY'))"}>}[# QN Open]),'#,##0')*100,'#,##0.0%       ●')))

Thanks........

[sunny_talwar]

Don't really know if it can be removed, except the part to remove Num function where it is not really needed within the if statement... but in any case, if you are able to provide a sample... we are more than happy to take a look for ways to improve it

[MK9885]

You are repeating same expression over and over again.... (same fields) Use ' >+<' or function to avoid creating a new set expression?

I do not see any difference between last 2 expression except  your % format which has a square box and a circle box.

Or store the expression in to variable and use variable and see if you get same result with good response time?

[nareshthavidishetty]

Hi,

Thanks for the inputs.

I'm getting one issue when i used the logic.

after using the logic the null values are showing as 0.0%.

Thanks..

[MK9885]

Null values show show some thing else or there should be no null values?

Not sure if this will work but maybe use [# QN Open]-={0} in your set?

Or maybe an expert like Sunny can help you?