Qlik Sens - Dividing Sums by assigning variables

[YG]

Hi All,

I have a table below table, I want to divide  FY19 =112.10/FY20 = 90.40. I am new to this, How do I assign  x(FY19 =112.10) and y(FY20 = 90.40) So I can get KPI value. I do not like to hard code the values as FY20 is on going and will change as it progress. So would like to do as an expression. Anyone can help would be appreciated 

2 columns table

[Anil_Babu_Samineni · Accepted solution]

Create one field in script like

Year(Num#(Right(FYear,2))) as FYear_Copy

Then, In the KPI you can define as below

Num(Avg({<FYear_Copy={$(=Max(FYear_Copy-1))}>} Resolve_time)/3600, '##.00')/Num(Avg({<FYear_Copy={$(=Max(FYear_Copy))}>} Resolve_time)/3600, '##.00')

Or, May be without field creation you can use

Num(Avg({<FYear={$(=MaxString(FYear))}>} Resolve_time)/3600, '##.00')/Num(Avg({<FYear={$(=MaxString(FYear))}>} Resolve_time)/3600, '##.00')

Or, If you already have some field related Financial Year, You can even try

Num(Avg({<Fin_Year={$(=Max(Fin_Year-1))}>} Resolve_time)/3600, '##.00')/Num(Avg({<Fin_Year={$(=Max(Fin_Year))}>} Resolve_time)/3600, '##.00')

[YG]

Thanks Anil. I chose to go with the ,

Num(Avg({<FYear={$(=MaxString(FYear))}>} Resolve_time)/3600, '##.00')/

Num(Avg({<FYear={$(=MaxString(FYear))}>} Resolve_time)/3600, '##.00')

Answer however was  = 1 

The out put was correct as per the pic its  (90.40/90.40)

It should be 112.10/90.40 instead

I tried,

Num(Avg({<FYear={$(=MaxString(FYear-1))}>} Resolve_time)/3600, '##.00')/

Num(Avg({<FYear={$(=MaxString(FYear))}>} Resolve_time)/3600, '##.00')

Also

Num(Avg({<FYear={$(=MaxString(FYear)-1)}>} Resolve_time)/3600, '##.00')/

Num(Avg({<FYear={$(=MaxString(FYear))}>} Resolve_time)/3600, '##.00')

both actually gave me an answer "-"

However,

Num(Avg({<FYear={$(=MinString(FYear))}>} Resolve_time)/3600, '##.00') = 158.38

Is there away to get MaxSrting()- 1?

[Anil_Babu_Samineni]

MaxString() returns based on Power of Character string length. I won't perform, IF i have same task. Instead how about other suggestion?

However, What it is returning MaxString(FYear) and MaxString(FYear)-1

If this is String (FYear) - MaxString(FYear)-1 this won't work.

[YG · Accepted solution]

Thanks Anil, I went down the path you suggested,

I created a new Dimension  called =  FYear_New using below  expression

Num#(Right(FYear,2)) 

Then Created a Measure :

Num(Avg({<FYear_New={$(=Max(FYear_New))}>} Resolve_time)/3600, '##.00')/
Num(Avg({<FYear_New={$(=Max(FYear_New-1))}>} Resolve_time)/3600, '##.00')

Thanks for all your help