Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can write variables with parameters in QlikView.
https://community.qlik.com/t5/QlikView-Documents/Qlikview-Parameterised-Variables/ta-p/1494184
Thanks. I hope this'll work in QS as well. I'll try.
Yeah, It works perfect in QlikSense as well.
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?
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') )