Power BI formula translate to Qlik Sense

[Julia_Velinova]

Dear All,

I need advice on how to make a Power bi formula work in Qlik Sense. I have been trying to come up with something similar in qlik sense for quite some time now, but it never provided the flexability i had in Power Bi.

I need to apply percentile to a data set and be able to change dinamicly. If the % i am applying is 5% of 100 rows = 5rows, i need it to take the same 5% percentile when i filter for example on 50 rows and get 2.5 rows and so on.

Here is the formula in BI that worked:

TTR =

VAR LowerValue = CALCULATE(PERCENTILE.INC ('Table'[Index], .03), ('Table'))

VAR UpperValue = CALCULATE(PERCENTILE.INC ( 'Table'[Index], .95 ), ('Table'))

RETURN

CALCULATE(AVERAGE('Table'[Time]), AND('Table'[Index] >= LowerValue,'Table'[Index] <= UpperValue))

Really appreciate if anyone has any ideas.

Best regards,

Julia

[ArnadoSandoval]

Hi @Julia_Velinova 

It took me a while to prepare this reply as I was fighting with QlikSense to implement a solution; there are two implementation alternatives, the attached Qlik Application (qvf file) includes both of them, actually, I managed to implement one of the alternative, as I explain.

User Interface/Front End solution:

• It requires implementing variables to calculate: LowerValue, UpperValue, Count, Sum and Average.
• The LowerValue and UpperValue variables were no problems, while Qlik refuse any attempt to implement the set analysis expression for the Sum() and Average results; perhaps, my own fault implementing them.
• The attached QVF's sheet 2: Results Phase 1, implements (or try to implement) the expected results with variables; I did not delete this logic because somebody else could be able to assist.

Script Driven Fields:

• The Load Script resolves the LowerValue, UpperValue and Count per dimension.
• You will find in the section "Load User Data" the table Stat performing these calculations, using the Fractile function; it creates the fields: Dim_LowerValue, Dim_UpperValue, among other less relevant fields.
• These fields appear in the UI as Scr_LowerValue and Scr_UpperValue.
• Using a Mapping table and "Preceding Load" we create the Flag field, this field is set to 1 when the RunTime Case Number, e.g. RT_Seq is within the Scr_LowerValue and Scr_UpperValue fields.
• The LowerValue expression is:

If(Count(Case_Number) > 1, Floor(fractile([RT_Seq], 0.05)), '-')

• If we have one or less Cases in a dimension (<=1) it returns a dash.
• If we have more than 1 Case ( > 1 ) we calculate the 5% fractile on the RT_Seq (run time Case Number)
• We round down (Floor) the fractile when its result is by example 4.9; this value is the lower limit, but because "RT_Seq" are integers we rounded it to 4. (Note: here it is up to your team to keep the rounding down, or implement the calculation driven by the calculated fractile, this could be a business decision, but your expected results is rounding down the fractile)
•  The UpperValue expression is:

If(Count(Case_Number) > 1,
    If( Count(Case_Number) = Ceil(fractile([RT_Seq], 0.95))
      , Floor(fractile([RT_Seq], 0.95)),Ceil(fractile([RT_Seq], 0.95)))
  , '-')

• If we have one or less Cases in a dimension (<=1) it returns a dash.
• If the number of cases (Count) in the dimension is equal to the rounded up (Ceil) 95% fractile, e.g. 100% of the records in the dimension, we have to round down (Floor) the 95% fractile, otherwise, we keep the round up (Ceil) value.
• This rules satisfice the Expected Result in your Excel file.

Results Phase 2 user interface:

• The resulting table: UserData now includes these new columns for us to implement the expected results on the user interface.
• The third sheet on the UI (Results Phase 2) implements the expected results driven from the Load Script fields.
• The expression calculating the Sum of the Resolution Time inside the Lower/Upper values is:

Sum({$<Flag={1}>} Resolution_Time)

• We sum all the Resolution Times for all the records inside the Lower/Upper value, e.g. those the load script flagged with 1.
• The expression calculating the Average of the Resolution Time inside the Lower/Upper values is:

Avg({$<Flag={1}>} Resolution_Time)

• Once again, Flag equal to 1 identifies the records in this expression.

Comments:

• Your expected result for the Sum of Resolution Times for Dimension 1 seems to be incorrect, as your calculation include the range between the records 4 and 75.
• Your expected result for the Average was calculated between the records 4 and 75, which seems incorrect to me.
• I did not try to find an explanation for the discrepancy on the Average for Dimension 3, first of all, as I do not have the formula you are using, but it seems to be that the record with a Resolution Time of zero was excluded from the Average calculation, and I do not know in detail the business rules you are using.

Well, this is all I can shared with you at the moment, I have some page reference which I will add in another reply tomorrow, as they are supporting concepts for this solution.

Please let me know your feed back, and if the Load Script implementation resolve your issue.

Hope this helps,

[Julia_Velinova]

Thank you for the effords! I will review the solution and let you know my feedback!

Best regards,

Julia

[robinh12]

I believe you misunderstood my question, as I believe the formulas you posted are for Qlik Sense, not Excel or Power BI, and my internet search for a function called "Fractile" in Excel/PBI yielded no results. I require them because in the attached solution, I forced the Lower and Upper values, which will not provide a good/solid foundation for the rest of the solution.