4 Replies Latest reply: May 25, 2016 2:56 AM by Gysbert Wassenaar

# Can I use variables in a Formula in Qlik Sense, see example below?

Dear all,

I would like to calculate the result of the margin in % fin a table, using the below variable

Sum({\$<[Prof-Gen Staffing unité]={'PS'}>} v_Margin_%_YTD)

Result should by like this:

Industry          PS     GS

A                    18%     20%

B                    10%     15%

C                    30%     14%

• ###### Re: Can I use variables in a Formula in Qlik Sense, see example below?

Yes you can. Starting with Qlik Sense 2.1 you can create variables in the Edit mode of sheets. See this video: Qlik Sense - Variables Interface (video)

Prior to version 2.1 you needed to create variables in the script with a LET or SET statement.

• ###### Re: Can I use variables in a Formula in Qlik Sense, see example below?

The variable " \$(v_Margin_%_YTD)" works. What I want is to display this result for 'PS' and 'GS' in a table.

• ###### Re: Can I use variables in a Formula in Qlik Sense, see example below?

Dear Gysbert,

I'm always tring to find a solution for the mentioned problem. The formula I would like to use is the following:

Sum({\$<[Prof-Gen Staffing unité]={'PS'}>}(\$(v_Margin_%_YTD)))

The formula for the varaible \$(v_Margin_%_YTD) is:

Num((((Sum({\$ <[Activité]={'Travail Temporaire'},Année={\$(=Max(Année))}>}[CA après ristourne]) - (Sum([Montant soumis AVS])+

(Sum([Montant soumis AVS]* (((If([Jours férié dans mission]='O',2.15,5.65)) * (((9.90 + if(IsNull(TauxChSocSuppl),0,TauxChSocSuppl))) / 100)) + (9.90 + if(IsNull(TauxChSocSuppl),0,TauxChSocSuppl)))/100) +

(((Sum([Montant soumis AVS] * [Taux SUVA] / 100)) + Sum ([Montant LPP payé]) +

(Sum([Montant Frais Form. Employeur]) + Sum([Montant Frais Exec. Employeur]) + Sum([Montant Retraite. Employeur]))) * (1+ ((If([Jours férié dans mission]='O',2.15,5.65)) / 100)))) + Sum([Montant non soumis AVS]))))

/ Sum({\$ <[Activité]={'Travail Temporaire'},Année={\$(=Max(Année))}>}[CA après ristourne])), \$(v_Format_Percentage_2))

IndustryGM% YTD PSGM% YTD GS
Automobil20%30%