# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor II

## Calculate Variation

Hello Guys!!

I hope you can help me!

I´m Using QlikSense but have some issues with expressions and variations (when using a calculated field).

1.- Dimensions, Filters and Measures:

Dimensions / Filters: RA Year, RA Month, Dia, CiudadDestino

Measures: TOTAL, Rental Days Cobranza

2.- Settings:

I am using the KPI chart/widget. As you know it shows the Main KPi and Secondary Kpi.

Main KPI: pretty easy, I just select the field I want show. Let´s choose TOTAL which are client contracts.

Count(TOTAL)

Secondary KPI: I want to show a variation. I use the following expression.

=num(Count({<DateID = {"<=\$(=Max(DateID))"}, [RA Year] = {\$(=Max([RA Year]))}> } (TOTAL)) / Count({<DateID = {"<=\$(=Max(DateID) - 12)"}, [RA Year] = {\$(=Max([RA Year]) - 1)}> } (TOTAL)) -1 , '0.00%')

It works.

Eventhough that I have several filters like "Ra Month" or  "CiudadDestino" (which are months and cities) when I apply all filters it works!

3.- Problem

But what should I do to use a calculated measure?

Calculated field: "RPD" which equals to: Sum(TOTAL)/Sum([Rental Days Cobranza])

I tried using the RPD formula and just put it inside the whole Secondary KPI. It just doesn´t work.

4.- Hypothesis 1 (doesnt work):

num(Sum({<DateID = {"<=\$(=Max(DateID))"}, [RA Year] = {\$(=Max([RA Year]))}> } Sum(TOTAL)/Sum([Rental Days Cobranza])) / Sum({<DateID = {"<=\$(=Max(DateID) - 12)"}, [RA Year] = {\$(=Max([RA Year]) - 1)}> } Sum(TOTAL)/Sum([Rental Days Cobranza])) -1 , '0.00%')

Labels (2)

• ### KPI Chart

8 Replies Partner

## Re: Calculate Variation

Can you explain a little more about how you add a calculated field? Did you do it in master items --> Dimensions --> create new?

Try add a master item for measures an use it in your object.

New Contributor II

## Re: Calculate Variation

Hello Xufei123

Yeap. Usually I used two ways to create a measure:

a) Master items>measures>Create new.

For example. If RPD is the relationship of total revenue and total days, then RPD=Revenue/Days.

I set up that field through the Master Items and gave it a name: RPD. It looks like: Sum (TOTAL)/Sum[Rental Days Cobranza]

Presto! I can use that Master Field for new situations. But I just cant use it in my formula. No way to just "drag and drop".

b) Thats why I decided to write the whole thing and see it it works.

Valued Contributor III

## Re: Calculate Variation

variableA=Sum({<DateID = {"<=\$(=Max(DateID))"}, [RA Year] = {\$(=Max([RA Year]))}> } Sum(TOTAL))

variableB=Sum({<DateID = {"<=\$(=Max(DateID))"}, [RA Year] = {\$(=Max([RA Year]))}> } Sum([Rental Days Cobranza]))

variableC=variableA/variableB

same for denomerator

later you try to have variablC/variableF

just different way

Channa
New Contributor II

## Re: Calculate Variation

Thanks Channa.

Variables might be another way.

I created the variable RPD=Sum(TOTAL)/Sum(Rental Days Cobranza).

Then I went to the KPI Chart and set the Variable inside the formula (it auto appeared as a Variable while I was typing)

"\$(RPD)"

but didnt work.

I attach the file sample data. If you could take a look it would be great.

Valued Contributor III

## Re: Calculate Variation

Try to check attach

Channa
New Contributor II

## Re: Calculate Variation

Thanks Channa!, yes is another form to show KPIS!

One KPI for Current Year Total Sales: Sum({<DateID = {"<=\$(=Max(DateID))"},[RA Year] = {\$(=Max([RA Year]))}> } TOTAL)

One KPI for Previous Year Total Sales: Sum({<DateID = {"<=\$(=Max(DateID) - 12)"},[RA Year] = {\$(=Max([RA Year]) - 1)}> } TOTAL)-1

One KPI for Variation: Sum({<DateID = {"<=\$(=Max(DateID))"},[RA Year] = {\$(=Max([RA Year]))}> } TOTAL)/
(Sum({<DateID = {"<=\$(=Max(DateID) - 12)"},[RA Year] = {\$(=Max([RA Year]) - 1)}> } TOTAL)-1)

It works.

But how do you do it if you want to calculate the variation of Sum(TOTAL)/Sum([Rental Days Cobranza])?

Check pic.

Valued Contributor III

## Re: Calculate Variation

try attach if i understood

Channa
New Contributor II

## Re: Calculate Variation

Thanks Channa,

Almost.

I guess it should be something like, but it doesn´t work yet:

Sum({<DateID = {"<=\$(=Max(DateID))"},[RA Year] = {\$(=Max([RA Year]))}> } TOTAL)/
(Sum({<DateID = {"<=\$(=Max(DateID))"}, [RA Year] = {\$(=Max([RA Year]))}> } [Rental Days Cobranza]))/
Sum({<DateID = {"<=\$(=Max(DateID))"},[RA Year] = {\$(=Max([RA Year])-1)}> } TOTAL)-1/
(Sum({<DateID = {"<=\$(=Max(DateID))"}, [RA Year] = {\$(=Max([RA Year])-1)}> } [Rental Days Cobranza])-1)