Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
abhishek_mitra
Contributor II
Contributor II

Can I write formulas as user defined functions in qlik sense?

Hi,

 

I have a formula (to calculate a measure for graphs) like this:

 

=if(only({1<P_sInd_1 = {1}>}P_fInd_1) = 1,
    Num(
        if(GetSelectedCount(P_D[1]_1)>0,
       
          ((Sum({$<P_MOB_1 = {$(postMOB)}>}P_M[$(P_STM1)]_1)
            /Sum({$<P_MOB_1 = {$(postMOB)}>}P_M[$(P_STd1)]_1))
            -
            (Sum({$<P_MOB_1 = {'$(preMOB)'}>}P_M[$(P_STM1)]_1)
            /Sum({$<P_MOB_1 = {'$(preMOB)'}>}P_M[$(P_STd1)]_1)))
          /
          (Sum({$<P_MOB_1 = {'$(preMOB)'}>}P_M[$(P_STM1)]_1)
            /Sum({$<P_MOB_1 = {'$(preMOB)'}>}P_M[$(P_STd1)]_1)),

          ((Sum({$<P_MOB_1 = {$(postMOB)}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STM1)]_1)
            /Sum({$<P_MOB_1 = {$(postMOB)}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))
            -
            (Sum({$<P_MOB_1 = {'$(preMOB)'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STM1)]_1)
            /Sum({$<P_MOB_1 = {'$(preMOB)'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1)))
          /
          (Sum({$<P_MOB_1 = {'$(preMOB)'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STM1)]_1)
            /Sum({$<P_MOB_1 = {'$(preMOB)'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))),
    '0.00%'),
    Num(
        if(GetSelectedCount(P_D[1]_1)>0,
       
          (Sum({$<P_MOB_1 = {$(postMOB)}>}P_M[$(P_STM1)]_1)
          /Sum({$<P_MOB_1 = {$(postMOB)}>}P_M[$(P_STd1)]_1))
          -
          (Sum({$<P_MOB_1 = {'$(preMOB)'}>}P_M[$(P_STM1)]_1)
          /Sum({$<P_MOB_1 = {'$(preMOB)'}>}P_M[$(P_STd1)]_1)),
         
          (Sum({$<P_MOB_1 = {$(postMOB)}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STM1)]_1)
          /Sum({$<P_MOB_1 = {$(postMOB)}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))
          -
          (Sum({$<P_MOB_1 = {'$(preMOB)'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STM1)]_1)
          /Sum({$<P_MOB_1 = {'$(preMOB)'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))
          ) * 10000,
    '#,##0')
)

 

 

and for 12 charts I have the same formula, only 3 parameters change:

1. P_sInd_1 = {1}. P_sInd_1 goes from 1 to 12

2. P_STM1 changes from P_STM1 to P_STM12

2. P_STd1 changes from P_STd1 to P_STd12

 

Is there any way to write the formula as a function and pass these 3 parameters into it?

 

Thanks,

Abhishek

Labels (4)
6 Replies
techvarun
Specialist II
Specialist II

abhishek_mitra
Contributor II
Contributor II
Author

Thanks. I hope this'll work in QS as well. I'll try.

techvarun
Specialist II
Specialist II

Yeah, It works perfect in QlikSense as well.

Vegar
Partner
Partner

It will work, but you can't pass it through an measure master object, you will need to write the expressions with parameters manually.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
abhishek_mitra
Contributor II
Contributor II
Author

A master object would've been the best solution for me, but I'd be happy even if I could write a formula and then use it in creating the 12 measures by passing my 3 parameters. Is there a possibility to do that?

Vegar
Partner
Partner

Create an variable vExpression with the following value.Then call it from your objects using =$(vExpression(parameter1, parameter2, parameter3))

=if(only({1<P_sInd_1 = {1}>}P_fInd_1) = 1,
    Num(
        if(GetSelectedCount(P_D[1]_1)>0,
       
          ((Sum({$<P_MOB_1 = {$1}>}P_M[$3]_1)
            /Sum({$<P_MOB_1 = {$1}>}P_M[$(P_STd1)]_1))
            -
            (Sum({$<P_MOB_1 = {'$2'}>}P_M[$3]_1)
            /Sum({$<P_MOB_1 = {'$2'}>}P_M[$(P_STd1)]_1)))
          /
          (Sum({$<P_MOB_1 = {'$2'}>}P_M[$3]_1)
            /Sum({$<P_MOB_1 = {'$2'}>}P_M[$(P_STd1)]_1)),

          ((Sum({$<P_MOB_1 = {$1}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$3]_1)
            /Sum({$<P_MOB_1 = {$1}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))
            -
            (Sum({$<P_MOB_1 = {'$2'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$3]_1)
            /Sum({$<P_MOB_1 = {'$2'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1)))
          /
          (Sum({$<P_MOB_1 = {'$2'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$3]_1)
            /Sum({$<P_MOB_1 = {'$2'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))),
    '0.00%'),
    Num(
        if(GetSelectedCount(P_D[1]_1)>0,
       
          (Sum({$<P_MOB_1 = {$1}>}P_M[$3]_1)
          /Sum({$<P_MOB_1 = {$1}>}P_M[$(P_STd1)]_1))
          -
          (Sum({$<P_MOB_1 = {'$2'}>}P_M[$3]_1)
          /Sum({$<P_MOB_1 = {'$2'}>}P_M[$(P_STd1)]_1)),
         
          (Sum({$<P_MOB_1 = {$1}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$3]_1)
          /Sum({$<P_MOB_1 = {$1}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))
          -
          (Sum({$<P_MOB_1 = {'$2'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$3]_1)
          /Sum({$<P_MOB_1 = {'$2'}, P_D[1]_1={'<=$(maxVintageSummary)'}>}P_M[$(P_STd1)]_1))
          ) * 10000,
    '#,##0')
)

 
Plees ekskuse my Swenglish and or Norweglish spelling misstakes