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

[abhishek_mitra]

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

[techvarun]

You can write variables with parameters in QlikView.

https://community.qlik.com/t5/QlikView-Documents/Qlikview-Parameterised-Variables/ta-p/1494184

[abhishek_mitra]

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

[techvarun]

Yeah, It works perfect in QlikSense as well.

[Vegar]

It will work, but you can't pass it through an measure master object, you will need to write the expressions with parameters manually.

[abhishek_mitra]

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]

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