topic Re: Sum and Group by in QlikView

Sum and Group by

Fri, 11 Apr 2014 11:28:25 GMT

Hi,

I am very new in Qlikview development.

I am trying to do a 'Group by' based on [sales year] and [sales month] to find the monthly Sales of each customer in Script.

But I am getting 'Invalid expression' error on following script.

CustomerMonthlySales:

LOAD

[Customer Number],

[VE Posting Year] AS SalesYear,

[VE Posting Month] AS SalesMonth,

SUM([Sales Amount (Actual)]) AS MonthlySales

Resident Fact Group BY [VE Posting Year],[VE Posting Month]

Re: Sum and Group by

fernando_tonial — Fri, 11 Apr 2014 11:31:45 GMT

Hi, You need put all field without agregation.

Try this.

CustomerMonthlySales:
LOAD
[Customer Number],
[VE Posting Year] AS SalesYear, 
[VE Posting Month] AS SalesMonth,
SUM([Sales Amount (Actual)]) AS MonthlySales
Resident Fact 
Group BY [Customer Number],[VE Posting Year],[VE Posting Month];

Best Regards.

Tonial.

Re: Sum and Group by

Fri, 11 Apr 2014 11:47:19 GMT

Thank you very much Tonial. That's worked....

Re: Sum and Group by

its_anandrjs — Thu, 30 Oct 2014 11:14:59 GMT

You have to include all the field in the Group by that you use

CustomerMonthlySales:

LOAD

[Customer Number],

[VE Posting Year] AS SalesYear,

[VE Posting Month] AS SalesMonth,

SUM([Sales Amount (Actual)]) AS MonthlySales

Resident Fact Group BY

[Customer Number],[VE Posting Year],[VE Posting Month];

Regards

Anand

Re: Sum and Group by

ashfaq_haseeb — Thu, 30 Oct 2014 11:19:40 GMT

Hi,

If you need to omit group by then below

CustomerMonthlySales:

LOAD

[Customer Number],

only([VE Posting Year]) AS SalesYear,

only([VE Posting Month]) AS SalesMonth,

SUM([Sales Amount (Actual)]) AS MonthlySales

Resident Fact Group BY

[Customer Number];

Regards

ASHFAQ