Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

elias_lohilahti
New Contributor II

Problems on calculating a daily sum and then a monthly maximum

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";

1 Solution

Accepted Solutions
elias_lohilahti
New Contributor II

Re: Problems on calculating a daily sum and then a monthly maximum

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.

6 Replies

Re: Problems on calculating a daily sum and then a monthly maximum

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";

elias_lohilahti
New Contributor II

Re: Problems on calculating a daily sum and then a monthly maximum

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.

Re: Problems on calculating a daily sum and then a monthly maximum

Can you try this?

Sum(a)/Sum(b) as dailysum


Note - Where a and b fields are ??

dwforest
Valued Contributor

Re: Problems on calculating a daily sum and then a monthly maximum

elias_lohilahti
New Contributor II

Re: Problems on calculating a daily sum and then a monthly maximum

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.

elias_lohilahti
New Contributor II

Re: Problems on calculating a daily sum and then a monthly maximum

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!