Sum between different date ranges

[Anonymous]

Hello,

I've got problem i got

Shop,Items, Stocktaking(date)

1,1,2015-11-25

1,2,2016-09-01

1,3,2016-10-18

I'm using pivot table and I'm trying to get value of stock(income and expenditure) but when i'm using expression :

Sum({<Year=, Month=, Quarter=, Week=, Data=,Day=,

DataNum={">=$(=Num(aggr(max(Stocktaking),Shop,Items)))<=$(=Num(Today()))"}>}Incomes)

It's doing it wrong, becouse when it finds different dates (stocktaking dates) it shows in sum as '-' and summing it from 2015-01-01(first date in DB). If i select just item 1 it shows ok, if i do not select anything it shows wrong.

Someone any idea how to solve it?

Regards,

Grzegorz

[sunny_talwar]

Are you looking for a max date by Shop and Item? If that is not the case, try this

Sum({<Year=, Month=, Quarter=, Week=, Data=,Day=, DataNum={">=$(=Max(Stocktaking))<=$(=Num(Today()))"}>}Incomes)

Otherwise, provide more details?

[jyothish8807]

Hi Grzegorz,

Try like this;

Sum({<Year=, Month=, Quarter=, Week=, Data=,Day=, DataNum={">=$(=num(Max(total Stocktaking)))<=$(=Num(Today()))"}>}Incomes)

Regards

KC

[Anonymous]

Hi guys, thanks for answer.

The problem is that every single item in every single shop can have different Stocktaking date it's why im aggr dates by items and by shops.

and value of stock im counting as value after stocktaking and adding income minus expenditure till now.

If i will do just max(Stocktaking) it will show max date for every item same when i got :

Shop,Item,Stocktaking(date)

1,1,2015-11-25

1,2,2016-09-01

1,3,2016-10-18

It will show for item 1,2,3 date correctly but it will count from 2016-10-18 till Today

Regards,

Grzegorz

[sunny_talwar]

May be try this:

Sum(Aggr(FirstSortedValue(Incomes, -Stocktaking), Shop, Item))

[Anonymous]

Hi Sunny,

Thanks for replyin,

I tried but it shows just zeros.

Attaching sample maybe it will make it a lil bit more clear what problem im facing.

Regards,

Grzegorz.

[sunny_talwar]

Does this look like what you wanted?

Expression used:

Sum(Aggr(FirstSortedValue(Value, -Date1), Shop, Items))

[Anonymous]

Yes,i think so thank you very much!

I have check it for one more record for Item 1, Shop 1. And it takes just last value. when it should sum between stocktaking date till last day.

Attaching sample with this one more record.

Regards,

Grzegorz

[sunny_talwar · Accepted solution]

How about this?

Sum(Aggr(If(Stocktaking <= Date1, Value), Shop, Items, Date1))

[Anonymous]

It's exacly what i need.

Thank you very much.