1 Reply Latest reply: Jan 15, 2018 6:39 PM by Stefan Wühl RSS

    Sum conditional

    Enrique Rodenas Ros

      Hello...

      I am new to qlik sense, and I need help.

      I have some data in excel with the following structure:

       

      ProductProjectSales
      7000011000
      7000012000
      7000023000
      7000011500
      7000043000
      7000024000
      700004

      1000

      7000022000

       

      When I select project 001, the project 004 must be added to project 001 according to a percentage (60%), while the rest must be added to project 002 (40%). For example:

       

      • If I select project 001, I should add:       1000 + 2000 + 1500 + (3000 * 0.6) + (1000 * 0.6) = 6.900
      • If I select project 002, I should add:       3000+ (3000 * 0.4) +4000+ (1000 * 0.4) + 2000 = 10,600

       

      Thanks.....

        • Re: Sum conditional
          Stefan Wühl

          Maybe something like

           

          LOAD Product,

          Project,

               If(Project = '004','00'&Iterno(),Project) as NewProject,

               If(Project = '004',If(Iterno()=1,0.6,0.4),1)*Sales as Sales

          FROM

          [https://community.qlik.com/thread/287902]

          (html, codepage is 1252, embedded labels, table is @1)

          While Iterno() <= If(Project = '004',2,1);

           

          Now your Project 004 records are split into two records and assigned to 001 and 002 accordingly.

          You can make your selections in NewProject to filter these two projects with the assigned values, or use Project to get the original sums.