3 Replies Latest reply: Jan 15, 2017 6:24 PM by Sunny Talwar RSS

    Sumif Function in Qlikview

    Ahmad Abdolkhalegh

      Hi Everyone,


      I want to create a sumif function in qlikview like excel.here is my data,the formula i use in excel and my formula in qlikview.


      i have a sales table like this(of course with a lot more information):


       

      BranchChannelInvoice numberDifference from Invoice date till today(Day)Accounts Receivable (Sales)
      ARetail1351000
      AHospital215810000
      BRetail515200
      BHospital718020000
      CRetail1150500
      CHospital1512025000


      I have some rules to create Overdue for this channel; in retail i only want to sum those which column 4 is above 30 days and in hospital above 150 days. in excel i write my formula like this:

      if(and(channel="Retail",Days>30),sum(Sales),if(and(channel="Hospital",Days>150),Sum(Sales),0))


      Now in Qlikview, i tried using set analysis like this:


      sum( {$<Channel={Retail},Days={'>30'}>}Sales)

      sum( {$<Channel={Hospital},Days={'>150'}>}Sales)

      but i have to create two expression and for example if i want to create a pivot table, for subtotal i always have zero and i think its because of in total i don't have one dimension(Channel)


      I would be grateful if anyone can help me on this issue


      Thanks in Advance