Formula Improvement

[lockematthewp]

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

[jwjackso]

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)

[jwjackso]

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.

[lockematthewp]

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?

[lockematthewp]

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.

[jwjackso]

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

[Brett_Bleess]

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