topic Re: Partial sum in incorrect for table in QlikView

Partial sum in incorrect for table

Dipti123 — Wed, 08 Oct 2025 09:51:26 GMT

I have this table where i want to show on first day of month the cumulative amount

below expression i Used but i gives me incorrect total

If(
Date([Data ordine]) = MonthStart(Today()) and
Month([Data ordine]) = Month(Today()) and
Year([Data ordine]) = Year(Today()),

Sum(Total If(
Archivio = 'O' and
Date([Data ordine]) <= MonthStart(Today()) and
Month([Data consegna]) = Month(Today()) and
Year([Data consegna]) = Year(Today()),
Importo
)),
Sum({<Archivio = {'O'}>} Importo)
)

in fonrt of 01/10/2025 it is sgowing correct value but its doesnot add it in partial sum wha is solution to this
Please help ?

Re: Partial sum in incorrect for table

Dipti123 — Wed, 08 Oct 2025 09:59:59 GMT

Many many thanks in advance for any help anyone can offer me.

Re: Partial sum in incorrect for table

marcus_sommer — Thu, 09 Oct 2025 13:02:51 GMT

By using a simple: sum(Field) the partial result is always the sum of all single rows. But as far as any conditions are applied or any kind of rates like: sum(Field) / count(Field) the partial results are NOT mandatory the sum of the rows.

In such cases you may need an aggr() wrapping to enforce a summing of the rows. This would look like:

sum(aggr(YourExpression, Dim1, Dim2))

whereby Dim1 & Dim2 are placeholder for the dimensional context in which the calculation should be performed (mostly the dimension of the object - but not mandatory).

Re: Partial sum in incorrect for table

Dipti123 — Mon, 13 Oct 2025 08:58:29 GMT

sum(aggr(If(
Date([Data ordine]) = MonthStart(Today())and
Month([Data ordine]) = Month(Today()) and
Year([Data ordine]) = Year(Today()),
Sum(Total If(
Archivio = 'O' and
Date([Data ordine]) <= MonthStart(Today()) and
Month([Data consegna]) = Month(Today()) and
Year([Data consegna]) = Year(Today()),
Importo
)),
Sum({<Archivio = {'O'}>} Importo)
), Year([Data ordine]), Month([Data ordine]),Day([Data ordine])))

it return Zero

Re: Partial sum in incorrect for table

PrashantSangle — Mon, 13 Oct 2025 09:24:42 GMT

I'm not sure, but I can see lots of issue/scope for optimization in your expression

1: if Date([Data ordine]) = MonthStart(Today()) is true it mean it is from same year & same month only then why you are doing additional 2 comparisons by adding "Month([Data ordine]) = Month(Today()) and
Year([Data ordine]) = Year(Today())"

2: one 1st condition is true then inside that you are comparing Date([Data ordine]) <= MonthStart(Today()) this condition, which is never going to be true, so your inside sum is never going to work

3: it is not working because you are writing naked expression in aggr()

syntax like Sum(aggr(Sum(),fieldName1,FieldName2))

Regards,

Prashant Sangle

Re: Partial sum in incorrect for table

Dipti123 — Mon, 13 Oct 2025 09:39:05 GMT

On the first day of the month (01/10/2025):
The value should represent a cumulative delivery total where:
- Order date (Data ordine) ≤ 01/10/2025
- Delievery Month (Data Consegna)= October 2025
- Archivio='O'
On all other days of October:
The value should represent the individual daily delivery amount for that specific delivery date.
In the partial sum or total row:
Qlik should add up the cumulative value from the 1st day + all individual day amounts for the rest of the month.

this is what imtrying to achieve

Please guide it wil help me alot @PrashantSangle

Re: Partial sum in incorrect for table

marcus_sommer — Mon, 13 Oct 2025 09:37:29 GMT

The 3. from @PrashantSangle means more precise: each defined dimension in an aggr() as well as in TOTAL or a set analysis (the left part) must be native fields - expressions won't be supported in these places.

Beside this it's not recommended to use calculated dimensions like: year(MyDate) within the UI else defining them appropriate within the data-model which is quite simple by period-information with a master-calendar.

Re: Partial sum in incorrect for table

Dipti123 — Mon, 13 Oct 2025 09:47:20 GMT

Can you please guide how can i add it using script as im new to qlikview

It will helpful .Please

Re: Partial sum in incorrect for table

Dipti123 — Mon, 13 Oct 2025 09:59:27 GMT

sum(aggr(If(
Date([Data ordine]) = MonthStart(Today()),
Sum(Total If(
Archivio = 'O' and
Date([Data ordine]) <= MonthStart(Today()) and
Month([Data consegna]) = Month(Today()) and
Year([Data consegna]) = Year(Today()),
Importo
)),
Sum({<Archivio = {'O'}>} Importo)
), Girono)

correct ? @PrashantSangle

Re: Partial sum in incorrect for table

marcus_sommer — Mon, 13 Oct 2025 10:00:37 GMT

Take this as starting point:

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

Re: Partial sum in incorrect for table

Dipti123 — Mon, 13 Oct 2025 10:02:38 GMT

Okay .Thank you @marcus_sommer

Re: Partial sum in incorrect for table

Dipti123 — Mon, 13 Oct 2025 10:23:10 GMT

Could you please confirm ?
@PrashantSangle

Re: Partial sum in incorrect for table

Dipti123 — Mon, 13 Oct 2025 12:19:59 GMT

@marcus_sommer As per the link you shared I studied the post

FirstOrderDatePerMonth:
LOAD
Year([Data ordine]) AS YearDel,
Month([Data ordine]) AS MonthDel,
Min([Data ordine]) AS FirstOrderDate
RESIDENT FATTI
WHERE Archivio = 'O'
GROUP BY
Year([Data ordine]),
Month([Data ordine]);

Deliveries_Cumulative:
LOAD
[Data ordine],
[Data consegna],
Importo,
Archivio,
Year([Data ordine]) AS YearDel,
Month([Data ordine]) AS MonthDel
RESIDENT FATTI
WHERE Archivio='O';

JOIN (Deliveries_Cumulative)
LOAD
YearDel,
MonthDel,
FirstOrderDate
RESIDENT FirstOrderDatePerMonth;

Deliveries_Daily:
LOAD
[Data ordine],
[Data consegna],
'Daily' AS RecordType,
SUM(Importo) AS Importo_Result
RESIDENT FATTI
WHERE Archivio='O'
GROUP BY
[Data ordine],
[Data consegna];

Cumulative_Result:
LOAD
FirstOrderDate AS [Data ordine],
MonthStart([Data consegna]) AS [Data consegna],
'Cumulative' AS RecordType,
SUM(Importo) AS Importo_Result
RESIDENT Deliveries_Cumulative
WHERE [Data ordine] <= FirstOrderDate
GROUP BY FirstOrderDate, MonthStart([Data consegna]);

DROP TABLE FirstOrderDatePerMonth, Deliveries_Cumulative;
EXIT SCRIPT;

is this correct ?

Thanks

Dipti Ahire

Re: Partial sum in incorrect for table

marcus_sommer — Mon, 13 Oct 2025 12:50:30 GMT

It seems to be a transfer of the logic from the UI to the data-model - which is more as above hinted - but if it could be done here it should be done there. But I could not justify if it's working against your data-set and requirements.

Re: Partial sum in incorrect for table

PrashantSangle — Mon, 13 Oct 2025 16:34:39 GMT

any error are you getting after that.. I lost between brackets of that expression.