Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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
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.
Try to check attach
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.
try attach if i understood
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)