6 Replies Latest reply: Oct 6, 2010 1:18 PM by Chris Perry RSS

    Set Analysis / Aggregation - Summing While Ignoring Chart Dimensions

    Chris Perry

      Hi,

      This is my first time posting here. I've been using QlikView for about six months and love it. However, there are times when I just can't seem to get the results I want. I've scoured the forums in an attempt to answer the question myself but couldn't find something that directly related to my issue. If there is a solution already posted I apologize and would appreciate it if someone could point me to it. So, here goes!

      I've got a data set that looks something like this (I apologize for the comma-separated list, I tried to make it look prettier but ran out of width in the post):

      TX_ID, Account, Service Date, Detail Type, Adj Code, Adj Category, Amount, %Charge_Flag
      1, 100, 01-Jan-2010, Charge, Null, Null, 150, 1
      1, 100, 01-Jan-2010, Adjustment, 1080, Charity, -50, 0
      1, 100, 01-Jan-2010, Adjustment, 1041, Contractual, -30, 0

      I'd like to build a pivot table chart that shows dimensions, expanded as follows:
      Adjustment Category -> Adj Code -> Account -> Service Date

      The expressions for each row are supposed to be like these:
      Sum of total charges across the entire transaction (TX_ID)
      Sum of adjustment dollars for each adjustment code

      The pivot table (fully expanded) should look like this when I'm done:

      Adj Category Adj Code Account Service Date Charge Adj Amount
      Charity 1080 100 01-Jan-2010 $150 ($50)
      Contractual 1041 100 01-Jan-2010 $150 ($30)

      The problem is that I'm having a not-so-good time trying to get the charge to display on each
      row of the pivot table. I don't have the original charge amount on each row of the data, otherwise
      this would be easy. I figured I needed to do set analysis to get the value but that doesn't
      work because the charge row is not included in the data that makes up my pivot table.

      I tried using the AGGR() function and thought I had it working but ended up with an odd
      result. Here is the AGGR() function I used:

      Aggr(Max(%Charge_Flag * AMOUNT), TX_ID)

      There is only one charge per TX_ID so I thought that by aggregating over the whole TX_ID I
      would get the charge. However, my pivot table now displays like this:

      Adj Category Adj Code Account Service Date Charge Adj Amount
      Charity 1080 100 01-Jan-2010 $150 ($50)
      Contractual 1041 100 01-Jan-2010 - ($30)

      Note the NULL in the Charge column on the second row (Contractual).

      Is it possible to get the sum that I want here, or do I need to adjust my model to include
      the original charge on each row of my fact table?

      Thanks!

      Best Regards,
      Chris

        • Set Analysis / Aggregation - Summing While Ignoring Chart Dimensions
          Karl Pover

          Chris,

          Try something like sum({$<[Detail Type]={'Charge'}>} Total <Account,[Service Date]> Amount) for you Charge column.

          I don't understand completely how you want the pivot table to look if you have more than one TX_ID, but maybe the formula will work.

          Regards.

            • Set Analysis / Aggregation - Summing While Ignoring Chart Dimensions
              Chris Perry

              Hi Karl,

              Thanks so much for the quick response! I took your suggestion and adapted it to my chart. This is the expression I came up with:

               

              Sum({$<DETAIL_TYPE = {'1'}>} Total <ACCOUNT_ID, ORIG_SERVICE_DATE> AMOUNT)


              What's interesting is that while this expression appears to now calculate the charge correctly for each row in the pivot table there is now an extra row in my pivot table. This row has nulls for the Adjustment Category and Adjustment Code. I am assuming that this is the data from the charge line in my data set (with the nulls for the adjustment category and code). I don't want this to appear so I went to the adjustment category and adjustment code columns in the pivot table and set them to 'Suppress When Value Is Null'. This did indeed remove the line but it had the unfortunate effect of now evaluating my new charge expression to $0.00 for every row.

              With new expression, before suppressing nulls:

              Adj Category Adj Code Account Service Date Charge Adj Amt
              Charity 1080 100 01-Jan-2010 $150 ($50.00)
              Contractual 1041 100 01-Jan-2010 $150 ($30.00)
              - - 100 01-Jan-2010 $150 ($0.00)

              With new expression, after supressing nulls:

              Adj Category Adj Code Account Service Date Charge Adj Amt
              Charity 1080 100 01-Jan-2010 $0 ($50.00)
              Contractual 1041 100 01-Jan-2010 $0 ($30.00)

              The expression for Adj Amt, if relevant, is this:

               

              Sum({$} AMOUNT)


              If I have more than one TX_ID in my data set (which I do, there are plenty of transactions) the expression should roll up all the charges across all the transactions that involve that particular adjustment code. The expression you came up with seems to do this fine and I can expand out the 'Total <fields>' section if I need to add more dimensions. The challenge now is eliminating the line in the pivot table that has no relation to the adjustment codes.

              Any thoughts there?

              Best Regards,

              Chris