Visokio website     Downloads     Video tutorials     KnowledgeBase  
Pivot View: Sorting and Ranking aggregated records/fields? - Visokio Forums
Pivot View: Sorting and Ranking aggregated records/fields?
  • tagarw September 1, 2014 7:22AM
    I have one Pivot View having one field/column named [Selected Ratings].
    Please see attachment for the problem and help me with this. Let me know if you need more information.
  • tagarw September 1, 2014 7:23AM
    PF attached.
    query1.docx 82K
  •     paola September 1, 2014 8:08AM
    I believe you wish to select the 5 lowest absolute numbers, so it might be useful to add a formula field ABS(number), replacing the 'number' with the expression from the [Selected Ratings] field, then limit the number of displayed values in this field.

    RANK function can be used with or instead, depending on the requirement, selecting the descending order, so the values -36, -46 have higher rank value than -500, in order to select them in your top 5.
    RANK(value, field, isAscending, includeNulls, dataSubset)
  •     rajeshbalu29 September 3, 2014 9:16AM
    It would be great if you have an example for us to filter based on top 5 and bottom 5 as we tried the functions as suggested but couldn't get the result as we expected.

    Thanks & regards
    Rajesh B
  •     tjbate September 8, 2014 3:02PM
    Rajesh - Attached is a general solution to filtering and ranking top/bottom N subsets, and calculating sub-averages and sub-rankings.
  • triptiagarwal September 9, 2014 3:06AM
    Hi Thomas,
    I have made some small changes to the data in your file. Please check the problem that we are facing here....Now it is returning more then 5 values with N set to 5?
  •     tjbate September 9, 2014 4:00AM
    As has been discussed on the phone with Paola, you have added many duplicate records to the values to be ranked. The RANK function does not recognise duplicates explicitly.

    There is a difference between a RANK function and a DENSERANK function, as explained here:
  • triptiagarwal September 9, 2014 5:04AM
    I have values like this only. Any workaround of this??
  •     tjbate September 9, 2014 6:48AM
    If you need the DENSERANK function you and all your colleagues should vote for it.
  •     Bart September 9, 2014 7:20AM
    There is a workaround. Use javascript for ranking. It is 1 minute to write the code. Read values to array and return position+1 of given value. You will get DENSERANK this way.
    Capture.JPG 48K
  •     paola September 9, 2014 2:22PM
    There is workaround in Omniscope too, so I attach here modified file posted earlier. Use of variables "Top N" and "Bottom N" enables you to create dynamic results, updating the calculated table values, as well as the view titles. You can then hide empty values to focus on selected top/bottom results.
    Formulas used:

    Rank Desc
    RANK([Data Values],[Data Values], false, false)  
     #ranking values in descending order, so the highest value has rank 1
    One= 1
    Running Total =
    RUNNINGTOTAL([One], SUBSET([Rank Desc]))
    #identifying shared rank values 
    Dense Rank Desc =
    RANK([Data Values], [Data Values], false, false,
    SUBSET([Running Total],1,"="))
    #ranking every Data value only once 
    Dense Rank Asc=
    RANK([Data Values], [Data Values], true, false,
    SUBSET([Running Total],1,"="))  
    #Ranking ascending unique values 

    Top N values=
    IF([Dense Rank Desc]<=[Top N],
    "Top "+[Top N],
    #Variable "Top N" introduced to generate dynamic values
    Bottom N values=
    IF([Dense Rank Asc]<=[Bottom N],
    "Bottom "+[Bottom N],
    #Variable "Bottom N" introduced to generate dynamic values  
    View Header Titles:
    "{{ SUBSET_FIRSTNONNULL([Top N val]) }} - Dynamic value"

    "{{ SUBSET_FIRSTNONNULL([Bottom N val]) }} - Dynamic value"
  • triptiagarwal September 10, 2014 1:33AM
    Thank you Paola for your help on this. We are almost done with your help. Only one concern is pending here. Top 10 is returning 11 records and Bottom 6 is returning 12 records.

    If we are talking about top 10 , It sould be 10 in count irrespective of repeated values. Same for bottom 6 also.

    Please suggest.
  •     Bart September 10, 2014 4:55AM
    Just use aggregate option in the view where you are displaying your results.
  •     paola September 10, 2014 5:28AM
    Quick way - use move/keep feature to first remove empty cells (those outside of Top N), then click on value 1 in the [Running Total] field to 'keep' only one record per rank value.

    Alternatively use a formula:
    AND([Dense Rank Asc]<=[Bottom N],[Running Total]=1), "Bottom "+[Bottom N], null)
  • triptiagarwal September 10, 2014 6:22AM
    Thanks Poala and really sorry if my Question was incomplete/not clear. I don’t have to select 6 records as ‘one from each value of dense rank if repeated’. I need it in same order. All 1, all 2 ,all 3 and so on till count is 6.
  •     paola September 10, 2014 6:53AM
    It might be best if you posted a spreadsheet (small sample) with data and desired results.
  • triptiagarwal September 10, 2014 7:29AM
    sample.xlsx 16K
  •     paola September 16, 2014 10:41AM
    You should use the Sort block in the DataManager space so the records enter Omniscope ordered by your target field.
    Add formulas to create running total for the top/bottom values:
    Running total top=
    IF([Top N val]<>null,
    RUNNINGTOTAL([One], SUBSET([Top N val]),false),
    Running total bottom=
    IF([Bottom N val]<>null,
    RUNNINGTOTAL([One], SUBSET([Bottom N val])),

    Now you can use the filters to trim the selection to actual top 10 records, even if some of them share the same Rank value.
    You can use either filters to customise your selection, or the visual selection on the Bar/line view, that orders the Rank values on X axis and cumulative record count on Y.
    In the image here we can see that two records share rank 10. Because the stacking is done on 'per record basis', you will be able to select only one of the two.
    (File is attached)



It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership