topic Re: simplified set analysis + if expression in QlikView

simplified set analysis + if expression

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: simplified set analysis + if expression

sunny_talwar — Wed, 08 Aug 2018 11:35:17 GMT

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

Re: simplified set analysis + if expression

MK9885 — Wed, 08 Aug 2018 14:56:33 GMT

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?

Re: simplified set analysis + if expression

nareshthavidishetty — Thu, 09 Aug 2018 10:40:17 GMT

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

Re: simplified set analysis + if expression

MK9885 — Thu, 09 Aug 2018 12:21:17 GMT

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?