Duplicate summation

[#1]

Hi,

I was wondering: is there a way to avoid duplicate summation of an amount?  Right now I am loading in information from different days through incremental loads, however some accounts carry over from day-to-day.  For Example, say invoice number 100 is in the data load for yesterday and today. The invoice was for $100.  Right now the summation formula adds the two, even though they are the same, for a total of $200.  Is there a way to make Qlikview recognize they are the same and show a total of only $100?

[PrashantSangle]

Hi,

Try with aggr()

like

Aggr(Sum(filed),Invoice Number,DateField)

Regards,

[#3]

Where would I fit that into the code? Sorry I'm not much of a developer, I just somehow got placed in a job where I have to set up a dashboard.  Right now my code for the flag is:

table:

NoConcatenate load

if(peek([Document Number])=[Document Number],0,1) as flaglast,

*

Resident Table_

order by [Document Number], [As Of] desc;

//DROP Table [Table_];

[PrashantSangle]

Hi,

Use this in your front end.

Like When you create a Straight table.

There in expression tab you can use above code.

Regards,

[maxgro · Accepted solution]

change the script (hope to understand)

source:
load *, rowno() as id;
load * inline [
invoice, val, date
1, 100, 01/01/2014
1, 110, 02/01/2014
1, 120, 03/01/2014
1, 130, 04/01/2014
1, 140, 05/01/2014
2, 200, 01/01/2014
2, 210, 02/01/2014
2, 220, 03/01/2014
3, 300, 04/01/2014
4, 400, 30/04/2014
4, 400, 30/04/2014
4, 400, 05/01/2014
];

left join (source)
load
  invoice,
  max(date) as date,
  1 as flaglast
Resident source
group by invoice;

[#6]

perfect. thank you!