Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
dalvaradog
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%')

 

I´d appreciate your notes.

Labels (2)
8 Replies
Partner
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.

dalvaradog
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.

Channa
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
dalvaradog
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.

Channa
Valued Contributor III

Re: Calculate Variation

Try to check attach

Channa
dalvaradog
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.

Channa
Valued Contributor III

Re: Calculate Variation

try attach if i understood

 

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