Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
SystemError
Contributor II
Contributor II

How to count if expression is included in fractile

Hi everyone! 
I need to count for each group (rating) if Value is greater fractile 0.85, then 2. If  greater fractile 0.65 then 1.5 and so on. 
 Also I'm excluding filial that colored yellow. 

Im trying to do it by formula in visualisation but its not working proparly, I got zeros in value and 2 in Totals. 

if(

              sum({<[filial]  =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(AGGR(SUM({<[filial]  =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.85), 2,

               if(

               sum({<[filial]  =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(AGGR(SUM({<[filial]  =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.65), 1,

                                            0

    )

               )

SystemError_1-1672044525365.png

How can I achieve correct result? 

Labels (4)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Add the total keyword to fractile, or else it will simply aggregate on dimension value

 

=if(

              sum({<[filial]  =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial]  =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.85), 2,

               if(

               sum({<[filial]  =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial]  =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.65), 1,

                                            0

    )

               )

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

1 Reply
vinieme12
Champion III
Champion III

Add the total keyword to fractile, or else it will simply aggregate on dimension value

 

=if(

              sum({<[filial]  =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial]  =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.85), 2,

               if(

               sum({<[filial]  =- {'Total', 'Москва'}>}Ebitda) > FRACTILE(TOTAL AGGR(SUM({<[filial]  =- {'Total', 'Москва'}>}Ebitda),rating,[filial]),0.65), 1,

                                            0

    )

               )

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.