Formula Improvement

lockematthewp — Fri, 28 Jun 2019 17:50:25 GMT

I currently have a bar chart with the following formula:

=Num(
if (getselectedcount([~Period]) <> 0,
(if(wildmatch(','&Concat([~Period],',') & ',','*,Jan,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jan$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Feb,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Feb$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Mar,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Mar$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Apr,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Apr$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,May,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}May$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Jun,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jun$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Jul,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jul$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Aug,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Aug$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Sep,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Sep$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Oct,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Oct$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Nov,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Nov$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Dec,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Dec$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Q1,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jan$+Feb$+Mar$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Q2,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Apr$+May$+Jun$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Q3,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jul$+Aug$+Sep$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Q4,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Oct$+Nov$+Dec$), 0)),
(if(wildmatch(','&Concat([~Period],',') & ',','*,Jan,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jan$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Feb,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Feb$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Mar,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Mar$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Apr,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Apr$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,May,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}May$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Jun,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jun$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Jul,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Jul$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Aug,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Aug$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Sep,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Sep$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Oct,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Oct$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Nov,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Nov$), 0)+
if(wildmatch(','&Concat([~Period],',') & ',','*,Dec,*')=1, Sum({<[~Data Classification(B)] = {'Applied Hrs'}>}Dec$), 0)))
,'#,##0.00')

However, this chart takes a while to load. I believe it has to do with the multiple nested 'if' statements. If any one can help me improve this formula I would greatly appreciate it.

(It just returns the Applied Hours for the selected period (month or quarter). If none are selected, it shows all months.)

Re: Formula Improvement

jwjackso — Fri, 28 Jun 2019 19:01:12 GMT

Check out the P() Function.

It will work better if you are using a calendar. I'm not sure how you have the Jan$, Feb$ fields defined. But using the P() function will return the rows that have the period selected or all rows if no period is selected.

Sum({<[~Data Classification(B)] = {'Applied Hrs'},[~Period]=P([~Period])>} AmtValue)

Re: Formula Improvement

jwjackso — Fri, 28 Jun 2019 19:02:49 GMT

One other thing, Qlik evaluates every possible outcome of the IF statement. It assumes that at some time every outcome will occur, so it caches the results. When possible, don't use an IF statement.

Re: Formula Improvement

lockematthewp — Mon, 01 Jul 2019 20:12:28 GMT

Thanks for the help. Unfortunately I do not have a Period field with the months in it. Instead the values are listed as Jan$, Feb$, Mar$, etc. So each month is like its own separate field. Any other suggestions?

Re: Formula Improvement

lockematthewp — Mon, 01 Jul 2019 20:16:03 GMT

Actually, I do have a period field. But it is just for matching. For example, Period is Jan, Feb, Mar,.... and separately I have Jan$, Feb$, Mar$, ... all as separate fields. And I am not able to change the way this is set up as it is used in many other dashboards as well.

Re: Formula Improvement

jwjackso — Mon, 01 Jul 2019 20:17:29 GMT

Well if possible, using the crosstable function to change the table layout may make your life a lot easier.

Re: Formula Improvement

Brett_Bleess — Mon, 29 Jul 2019 19:43:07 GMT

If you are still searching for a solution, try the Design Blog area of Community as well, hundreds of how to and explanatory posts on different topics there that may be useful to you as well:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

topic Re: Formula Improvement in QlikView

Formula Improvement

Re: Formula Improvement

Re: Formula Improvement

Re: Formula Improvement

Re: Formula Improvement

Re: Formula Improvement

Re: Formula Improvement