4 Replies Latest reply: Jul 20, 2017 3:41 PM by Abdul Khan RSS

    Qlik Set Analysis with Concatenated table

    Abdul Khan

      Hi all,

       

      I am new to qlik. I have a concatenated table where member and order information has been combined for performance reason. Data model works good so far from speed perspective.

       

      I ran across a set analysis scenario that i can't figure out.

       

      my table looks like this

            

      sourceupload_keyorder_amountorder_segmentmember_monthupload_name
      c112anullprior
      c112bnullprior
      c112cnullprior
      m1nullnull1prior
      m1nullnull1prior
      m1nullnull1prior
      c221anullcurrent
      c221bnullcurrent
      c221cnullcurrent
      m2nullnull1current
      m2nullnull1current
      m2nullnull1current

       

       

      table has two different uploads (time frames) and member and claims data (source=c,m)

       

      I need to reports on order_segment dimension by order_amount divided by entire year's worth of member_months

       

      This is what i have searched up and it does not work as i am thinking that order_months do not relate to order segments. Totals return numbers but order segment dimensions don't

       

      SUM ({$<UPLOAD_KEY_year = {$(=max(UPLOAD_KEY_year) )}>} [UTILIZATION])  / SUM ({1}>} [Order_months])

       

      My result table would look like this 12/ total member_months=3 and 21 divided by same uploads total member months=3

       

      Can you please help.

       

         

      order segmentcurrent order/monthsprevious orders/month
      a47
      b47
      c4

      7