Last years sales - should be simple but not working

[chris1987]

I have a simple function that gets me current years sales, which works perfectly:

=sum(aggr([I_Net-ext-total],[I_bol-no]))

I now need to have a column next to it that shows "Previous Years Sales" - which I though was simple, but this returns 0:

=sum({<[FC Acct Year]={'2018'}>}aggr([I_Net-ext-total],[I_bol-no]))

I've also tried:

=sum({$<[FC Acct Year]={'2018'}>}aggr([I_Net-ext-total],[I_bol-no]))

But can't get the figure to return. I've no other filters.

Just a year filter on [FC Acct Year]

Any help would be appreciated

Cheers Chris

[pradosh_thakur]

Please check if the Aggr function is really needed?

try this may be by removing the single quotes

=sum({<[FC Acct Year]={2018}>}aggr([I_Net-ext-total],[I_bol-no]))

[chris1987]

Thanks for the reply, unfortunately the below didn't work.

I'm going to check the aggr() requirement as you suggested. I removed it and the value was exactly double of what I expected. So I'm thinking the structure is not correct and there are two entries per line instead of one.

Thanks for this, you've got me thinking that the problem is elsewhere 🙂

Cheers

Chris

[chris1987]

Yeah, two lines found in my Master Table, one for orders and one for invoice, as the order was invoiced in the same month the value was getting counted twice.

What I'll do now is add a flag to my  MasterTable called "Type" and then use the following:

=sum({<[FC Acct Year]={2018],[Type]="I",[I_Net-ext-total])

Thanks

Chris

[pradosh_thakur · Accepted solution]

Sounds good Chris.
you have put ']' after 2018. please change that top '}' and a couple of changes
=sum({<[FC Acct Year]={2018},[Type]={"I"}>} [I_Net-ext-total])

[chris1987]

Cheers, yeah sorry I rushed that reply a bit. Thanks again for your help, it's been a problem to me for days!