Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Cheers, yeah sorry I rushed that reply a bit. Thanks again for your help, it's been a problem to me for days!