Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dalvaradog
Contributor II
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
NZFei
Partner - Specialist
Partner - Specialist

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
Contributor II
Contributor II
Author

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
Specialist III
Specialist III

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
Contributor II
Contributor II
Author

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
Specialist III
Specialist III

Try to check attach

Channa
dalvaradog
Contributor II
Contributor II
Author

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
Specialist III
Specialist III

try attach if i understood

 

Channa
dalvaradog
Contributor II
Contributor II
Author

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)