Hi ,
Is there a equivalent expression instead of using top percent for cognos olap .
I woulld like to find the list of materials who contribute towards sale that is greater than 80 percent and less than 95 percent.
Except(topPercent (filter([Agency Sales Cube].[Items].[Items].[Item],Total([Qty] within set set([2019/Jul],[2019/Aug],[2019/Sep],[2019/Oct],[2019/Nov],[2019/Dec]))>0),95,[H0 Sales]),
topPercent (filter([Agency Sales Cube].[Items].[Items].[Item],Total([Qty] within set set([2019/Jul],[2019/Aug],[2019/Sep],[2019/Oct],[2019/Nov],[2019/Dec]))>0),80,[H0 Sales]))
The above expression is working but its not 100 percent right because the toppercent ranks the items based on a measure and if the measure has same value it considers in one group . I would basically like to know how can top percent be written in another form of expression manualy