Partial sum in incorrect for table

[Dipti123]

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 ?

[Dipti123]

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

[marcus_sommer]

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).

[Dipti123]

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

[PrashantSangle]

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

[Dipti123]

• 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 

[marcus_sommer]

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.

[Dipti123]

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

It will helpful .Please

[Dipti123]

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 

[marcus_sommer]

Take this as starting point:

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