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

[#1]

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%        

Thanks for your help!

[Gysbert_Wassenaar · Accepted solution]

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.

[#3]

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

Thanks for your help!

[#4]

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

Industry	GM% YTD PS	GM% YTD GS
Automobil	20%	30%

Thanks for your support!

Patrik

[Gysbert_Wassenaar]

Start a new discussion and explain what the problem is. A small Qlik Sense app that demonstrates the problem will be helpful.