1 Reply Latest reply on Jan 2, 2016 4:53 AM by Norbert Maijoor

    How to use aggregate function as a filter

    Shankar Mishra

      It Becomes a aggreagate calc.

       

      i am not able to use this as filter

       

       

      IF Sum( [Amount LCY])<=10000 THEN"0-10000"

      ELSEIF Sum( [Amount LCY])<>10001 and Sum( [Amount LCY]) <=30000 THEN  "10001-30000"

      ELSEIF Sum( [Amount LCY]) >30001 and Sum( [Amount LCY]) <=50000 THEN  "30001-50000"

      ELSEIF  Sum( [Amount LCY]) >50001 and Sum( [Amount LCY]) <=100000 THEN  "50001-100000"

      ELSEIF Sum( [Amount LCY]) >100001 and Sum( [Amount LCY]) <=500000 THEN  "100001-500000"

      ELSEIF Sum( [Amount LCY]) >500001 and Sum( [Amount LCY]) <=1000000 THEN  "500001-100000"

      ELSEIF Sum( [Amount LCY]) >1000001 and Sum( [Amount LCY]) <=5000000 THEN  "1000001-5000000"

      ELSEIF Sum( [Amount LCY]) >5000001 and Sum( [Amount LCY]) <=7500000 THEN  "5000001-7500000"

      ELSEIF Sum( [Amount LCY]) >7500001 and Sum( [Amount LCY]) <=10000000 THEN  "7500000-10000000"

       

       

      ELSE "Above 1Cr"

      END

        • 1. Re: How to use aggregate function as a filter
          Norbert Maijoor

          Shankar!

           

          Find my approach below and stored in attached workbook version 9.0

           

           

          Bins Amount LCY:

           

          IF  {fixed [ID]: sum([Amount LCY])}<=10000 THEN"0-10000"

          ELSEIF {fixed [ID]: sum([Amount LCY])} <=30000 THEN  "10001-30000"

          ELSEIF {fixed [ID]: sum([Amount LCY])}<=50000 THEN  "30001-50000"

          ELSEIF {fixed [ID]: sum([Amount LCY])} <=100000 THEN  "50001-100000"

          ELSEIF {fixed [ID]: sum([Amount LCY])} <=500000 THEN  "100001-500000"

          ELSEIF {fixed [ID]: sum([Amount LCY])} <=1000000 THEN  "500001-100000"

          ELSEIF {fixed [ID]: sum([Amount LCY])} <=5000000 THEN  "1000001-5000000"

          ELSEIF {fixed [ID]: sum([Amount LCY])} <=7500000 THEN  "5000001-7500000"

          ELSEIF {fixed [ID]: sum([Amount LCY])} <=10000000 THEN  "7500000-10000000"

           

          ELSE "Above 1Cr"

          END

           

          binsa.png