Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group By expression error?

Hi

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

3 Replies
Not applicable
Author

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;

er_mohit
Master II
Master II

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;

Not applicable
Author

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