Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
How about this?
Sum(Aggr(If(Stocktaking <= Date1, Value), Shop, Items, Date1))
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?
Hi Grzegorz,
Try like this;
Sum({<Year=, Month=, Quarter=, Week=, Data=,Day=, DataNum={">=$(=num(Max(total Stocktaking)))<=$(=Num(Today()))"}>}Incomes)
Regards
KC
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
May be try this:
Sum(Aggr(FirstSortedValue(Incomes, -Stocktaking), Shop, Item))
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.
Does this look like what you wanted?
Expression used:
Sum(Aggr(FirstSortedValue(Value, -Date1), Shop, Items))
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
How about this?
Sum(Aggr(If(Stocktaking <= Date1, Value), Shop, Items, Date1))
It's exacly what i need.
Thank you very much.