Sum of something not working

[#1]

Hey guys, will appreciate if anyone can help me on this.

I am trapped with this issue for almost 1 day and still couldn't solve it.

The thing is i am trying to sum up the cost and then group by month but it keeps saying invalid expression.

May I know which is the part that I have coded wrongly?

LOAD A as Item,

          B as Quantity,

          SUM(C) as Cost,

          D as Month,

          Month(D) as Monthly

          FROM

(ooxml, no labels, table is Total) Group By Month(D)

;

[Jason_Michaelides · Accepted solution]

You have to group by all non-aggregated fields just like any DB query language:

LOAD A as Item,

          B as Quantity,

          SUM(C) as Cost,

          D as Month,

          Month(D) as Monthly

          FROM

(ooxml, no labels, table is Total)

Group By

   A,

   B,

   D,

   Month(D);

(you may not need the Month(D) bit)

Hope this helps,

Jason

[#3]

You are such a lifesaver.

I'm still new to this so I will require as much help as possible.

Mind explaining the "group by all non-aggregated fields"?

I'm a little lost in this

[Jason_Michaelides]

No problem.

Sum(C) is an aggregation - it adds together all values of C with a new value for each group.  All the other fields being loaded (A,B,D) are not being aggregated so they must be part of the grouping, therefore in a bit of load script that aggregates a field, you should only load the aggregation and the fields to group by.  For example, the above will get a value of Sum(C) for every distinct combination of A&B&D.  If you want a value of Sum(C) for every distinct value of A only, you would use:

LOAD

     A

     ,Sum(C)

FROM...

GROUP BY A;

Geddit?

Hope this helps,

Jason

[#5]

I think i am finally getting the picture of what you meant.

Will definitely read up on SQL and Qlikview.

Thanks for the help!

[#6]

Hey Jason,

Another issue on the logic here:

I would like to load both the breakdown of the actual cost and the sum up of the actual cost (grouping by contract and month) thus I've uploaded from the source twice.

What is the thing i need to do to sum up the cost of all the jobs belonging to the same contract and months?

Actual:

LOAD "Bill-to Name" as CustName,

    Contract as "Contract No",

    Description as JobDesc,

    "No_" as JobNo,

    "PO_WO Value" as ActualCost,

    Month("Ending Date") as Month,

    Year("Ending Date") as EndYear

    WHERE Year("Ending Date") = year(today());

SQL SELECT "Bill-to Name",

    Contract,

    Description,

    "Ending Date",

    "No_",

    "PO_WO Value"

FROM database

WHERE "No_" LIKE 'J%';

TotalActual:

LOAD Contract as "Contract No",

    //"No_" as JobNo1,

    SUM("PO_WO Value") as TotalCost

    WHERE Year("Ending Date") = year(today()) group by Contract, month("Ending Date");

SQL SELECT Contract,

    "Ending Date",

    "No_",

    "PO_WO Value"

FROM database

WHERE "No_" LIKE 'J%';

[Jason_Michaelides]

Ok, I should probably have asked this earlier, but why are you aggregating in the script? Unless you are dealing with very large datasets, it is rarely beneficial to do this and you will lose some of the key benefits of QlikView. QV has very clever association and storage mechanisms and is a totally In-Memory (RAM) tool, so the speed of data access lends itself to these type of aggregations being performed on the fly. General practice is to load in all the transaction-level data, then perform the grouping and calculations in the charts themselves. This way you get your high-level view but are also able to drill right down into the most granular level of detail. All without going back to the source database at all.

So, in you case just load the first table above. Then, in you charts use Contract and month as your chart dimensions and Sum(ActualCost) as the expression. Now, no matter what other field you select a value in, the chart will update immediately to reflect the selections. All data is available this way - that's the key to true data discovery for your users.

Jason

PS - move the first WHERE clause down into the SQL - it should perform better.

PPS - consider creating a master calendar with all dates you could possibly need and all variations of these date (Day,Month,Quarter,Year etc) rather than using the date field in you transaction data. Youay not have a transaction on every date so some will be missing which can make charts misleading. There are lots of threads about this on the forum - have a look around. 

[#8]

Thanks for the reply, while i was trying to figure out just now, i realised that the month for the "TotalActual" table was not linked.

There are a lot of refining to do especially when I've just started on Qlikview.

I will try out your advise on the refining part.

By saying "master calender" do you mean building an inline table?

[Jason_Michaelides]

There is some simple code to dynamically create a master calendar between to dates. Just search in the forum.

Looking at your data model above, the best advise I can give you is to invest some time into properly understanding QlikView best practices and data modelling. There are some really good white papers on QlikView.com and lots of good articles in this forum. You should (for example) have a master calendar as we've said, plus you should not have synthetic keys which are created when you have more than one common field between tables. maybe look to concatemate your actual and budget tables together.

Take the time now - you won't regret it later 😉

Jason

[#10]

Yes Jason, I agree.

Gonna build up on my foundation first before I can venture out on having more complicated reports to work on.

Thank you so much for your help again.

Cheers,

JX