Problems on calculating a daily sum and then a monthly maximum

[Anonymous]

Hi!

I have data on single transactions, and I would need to sum daily amounts and then get a monthly maximum of them. I think it would be easy either to calculate daily sums from single transactions or to calculate monthly maximum from the daily sums, but I can't seem to find a way to do this kind of a two-dimensional aggregation e.g. in Bar Charts and Pivot Tables. Do I need to do some aggregations already in the Data Load editor? Tried to do as follows, but loading the data lead to an error.

LTB CONNECT TO [database]

[tablename]:

LOAD

[contractno]

Date([dateno]),

[dailysum];

SQL

SELECT "contractno", "dateno", SUM("a" / "b") AS "dailysum"

FROM "Database"."table1", "Database"."table2"

WHERE "contract1" = "contract2"

GROUP BY "dateno";

[Anil_Babu_Samineni]

Is that returning anything?

[tablename]:

LOAD

[contractno]

Date([dateno]) as dateno,

[dailysum];

         SELECT "contractno", "dateno", SUM("a" / "b") AS "dailysum"

FROM "Database"."table1", "Database"."table2"

WHERE "contract1" = "contract2"

GROUP BY "dateno";

[Anonymous · Accepted solution]

Thanks Anil for the helpful answer, that was also a part of the problem!

The actual problem, however, seemed to be in the GROUP BY statement. Apparently Qlik requires there to be all fields being loaded excluding the ones actually being aggregated. I'm also not sure if the way I was doing this with the SQL query was correct since I read that Qlik might not support aggregations and multiple tables within SQL SELECT statments, and also in the SUM-function the fields "a" and "b" are from different tables. I ended up loading and joining the two tables, and then doing the aggregation in a resident load into a new table, which worked perfectly. So, my script in the end looked like this:

[tablename]:

LOAD

[contractno],

[name],

[date],

Sum( / ) as [dailysum];

Resident

[temp_table]

Group by

[date],

[contractno],

[name];

DROP Table [temp_table];

Now having the daily sums in the data already, it is perfectly easy to do the monthly aggregation in the visualization, setting month as a dimension and Max(dailysum) as a measure in a table or a bar chart.

[Anil_Babu_Samineni]

Can you try this?

Sum(a)/Sum(b) as dailysum

Note - Where a and b fields are ??

[dwforest]

Pull in raw data and aggregate in Qlik using Aggr()

https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/ChartFunctions/aggr.htm

[Anonymous]

Thanks, I'll have to look into that!

This problem was solved by doing the aggregation in the data load script as I explained before, but I guess this could've been an easier way to do it.

[Anonymous]

I'm sorry, the message might have been unclear. The script in the last message was already a solution that I found out myself.

Even though fields a and b were originally in different tables, having joined those two tables into this temporary one (temp_table) Sum(a/b) seemed to work without a problem!