Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression Help ??

Hi

I need to calculate ‘Margin’ in a straight table. I am struggling to write the expression.

Can someone help me please ?

Details are below.

Margin = Net Sales – Net Costs

Net Sales =  Total Sales – OutputVAT

Total Sales = Sales * Pax

OutputVAT= Total Sales – (Total Sales / (1+vat))

Net Costs = Subtotal - InputVAT

Subtotal = Gross Costs – Commission

Gross Costs = Costs * Pax

Commission = (Gross Costs – Restoration Levy per booking) * ( Inside / 100)

Restoration Levy per booking = Restoration * Pax

InputVAT = (Subtotal – (Subtotal/(1+vat)))

Note:  Sales, Costs, Pax, Restoration, Inside, vat are fields

Many thanks

1 Reply
christian77
Partner - Specialist
Partner - Specialist

Hi:

Is that on a single table? (straight table does not take expressios, does it?)

1st       I use variables. Then I place the variable everywhere I need it. Changes are easier.

2nd      Use functions like Margin = sum(Net Sales) - sum(Net Costs)  (Use help. Type Functions. Search)

3rd       Avoid using the word 'Total' as a field, it is a reserved word

I guess lengthy refers to time. Calculatios are fast. Printing is slow. How many records per year? Well, limitate the table to a year or to a month. Open the Table Properties. Tab General. Calculating Condition: count(distinct Year) = 1 and count(distinct Month) = 1. Please, filter a Year and a Month in the Calculating Condition Message.

You´ll get more tips...