1 Reply Latest reply: Dec 14, 2017 11:18 PM by Anil Babu RSS

    Limiting 1 dimension on another dimensions top x

    Joshua Diffey

      So I have a qvw where by I am showing the top 10 customers by gross profit

      RankCustomerGross Profit
      1Customer A90,000
      2Customer B80,000
      3Customer C70,000
      ...
      10Customer J40,000

       

      For these top 10 customers I want to show a pie chart by income stream (eg. Private Sector, Government Sector, Mining Sector, etc.). So the dimension on the Pie Chart currently is Income Stream.

       

      So if the top 10 customers didn't include any income from the Mining Sector, I don't want them included in the Pie Chart.

      In addition I don't want to include any income from customers not in the top 10. So if out of the top 10 customers, $30,000 was spent on the Private Sector, that slice of the Pie Chart should be $30,000.

       

      Does anyone have any ideas?

       

      I assume I need to do something using the Rank Function, but I can't work out where/how is the best to do it.

       

      The expression is already a set analysis

      sum({<isParentJobClosed={0},[Period End Date]= {"<=$(=SelectedPeriodEnd)"}>+<isParentJobClosed={1},ParentJobCompleteDate={">=$(=SelectedFinancialYearStart)"},[Period End Date]= {"<=$(=SelectedPeriodEnd)"}> } SalesLineRetail)

        • Re: Limiting 1 dimension on another dimensions top x
          Anil Babu

          From given expression? Won't work for single degrade field. The highlight one ignores.

           

          SelectedPeriodEnd is the field or Variable?

           

          sum({<isParentJobClosed={0},[Period End Date]= {"<=$(=SelectedPeriodEnd)"}>+<isParentJobClosed={1},ParentJobCompleteDate={">=$(=SelectedFinancialYearStart)"},[Period End Date]= {"<=$(=SelectedPeriodEnd)"}> } SalesLineRetail)


          To answer your question, Perhaps this?


          sum({<isParentJobClosed={0},[Period End Date]= {"<=$(=SelectedPeriodEnd)"}>+<isParentJobClosed={1},ParentJobCompleteDate={">=$(=SelectedFinancialYearStart)"}, Customer = {"=Rank(Sum([Gross Profit),4)<=10"}> } SalesLineRetail)