topic Re: Group By expression error? in QlikView

Group By expression error?

Thu, 16 May 2013 13:36:49 GMT

I am trying to simply group some SUMS by an ID whilst if possilbe setting a date for a date range my first issue is an expression issue and I can't work out why and I can't get the dates set-up to work either.

I have an InvoiceLine table

with

IntEnrold, dteFromDate, dteToDate daily_total calculated using (curBasic) / (interval(dteToDate-dteFromDate, 'D')) AS curBasicDaily_Total,

I would like to produce a single table grouped by IntEnrolId with a SUM(daily_total) for each day.

My Code below if someone could suiggest where I might be going wrong it would be appreciated:

Invoice_x_Dates:
Load

    intEnrolId,
     SUM(InvoiceLine.curBasicDaily_Total) ,
     SUM(InvoiceLine.curDiscountDaily_Total) ,
     SUM(InvoiceLine.curDueDaily_Total) ,
     SUM(InvoiceLine.curCommissionBasicDaily_Total)

      Date(InvoiceLine.dteFromDate + IterNo() -1 ) AS INDate

      Resident InvoiceLine

       While IterNo() <= InvoiceLine.dteToDate - InvoiceLine.dteFromDate + 1

      Group By intEnrolId;

Many Thanks

David

Re: Group By expression error?

Thu, 16 May 2013 13:41:03 GMT

Invoice_x_Dates:
Load

    intEnrolId,
     SUM(InvoiceLine.curBasicDaily_Total) ,
     SUM(InvoiceLine.curDiscountDaily_Total) ,
     SUM(InvoiceLine.curDueDaily_Total) ,
     SUM(InvoiceLine.curCommissionBasicDaily_Total),

      Date(InvoiceLine.dteFromDate + IterNo() -1 ) AS INDate

      Resident InvoiceLine

       While IterNo() <= InvoiceLine.dteToDate - InvoiceLine.dteFromDate + 1

      Group By intEnrolId;

Re: Group By expression error?

er_mohit — Thu, 16 May 2013 13:47:00 GMT

Try this

Invoice_x_Dates:
Load

    intEnrolId,
     SUM(InvoiceLine.curBasicDaily_Total) ,
     SUM(InvoiceLine.curDiscountDaily_Total) ,
     SUM(InvoiceLine.curDueDaily_Total) ,
     SUM(InvoiceLine.curCommissionBasicDaily_Total) ,

      Date(InvoiceLine.dteFromDate + IterNo() -1 ) AS INDate   //If error comes below code and comment this one

     // Date(InvoiceLine.dteFromDate + rowno() -1 ) AS INDate
      Resident InvoiceLine

       While InvoiceLine.dteFromDate+iterno()-1<= InvoiceLine.dteToDate
            Group By intEnrolId;

Re: Group By expression error?

Thu, 16 May 2013 13:55:30 GMT

Thanks for the quick reply but still getting invalid Expression...however when I remove the SUMS and group by the dates part of the script does work!

confused still !!

Ps now sorted thanks..I had an error in my code I had not added all the group by fields I needed...thanks again