Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
prashantsanchet
Contributor

Ratio calculation from two table

Hi Team,

I have 2 table

Table 1 :   :Date,Country,Region,State,DailySales,MTDSales,QTDSales



Table 2: Date,Country,BudgetAmount

I want t create a on more fired which will be Margin% = (MTDsales value at selected Business Date) /  (Budget Amount of Monthstart date)

How we can calculate this in Load Script ?


If user select a for ex; 15th May 2018 then Margin % = (MTDsales value at 15th May 2018) / 9Budget Amount at 01st may 2018.)



3 Replies
YoussefBelloum
Esteemed Contributor

Re: Ratio calculation from two table

Hi,

if your Budget table is Budgeted per month, so in your chart you will need to use a Month field to be able to compare Sales Vs Budget.

If you want to do the calculation on the script, yo should Join the tables to be able to create the Margin% field.

you can also create the calculation on the chart using: sum(Sales)/sum(Budget)

prashantsanchet
Contributor

Re: Ratio calculation from two table

Hi ,

My both table are having daily level granular data.

I need to get this column for each  date

:

If user select a for ex; 15th May 2018 then formula is


Margin % = (MTDsales value at 15th May 2018) / Budget Amount at 01st may 2018)


How this can be achieve using script.

YoussefBelloum
Esteemed Contributor

Re: Ratio calculation from two table

Like what i said above, on the script you should have the sale field and the budget field on the same table, otherwise you will need to join the tables to be able to do something like this:

Table:

Load

.

Sales_field/budget field as margin%

.

From...

And use Sum(margin%) on your charts

Community Browser