topic Re: Sum between different date ranges in QlikView

Sum between different date ranges

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum between different date ranges

sunny_talwar — Thu, 03 Nov 2016 16:33:32 GMT

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?

Re: Sum between different date ranges

jyothish8807 — Thu, 03 Nov 2016 16:44:17 GMT

Hi Grzegorz,

Try like this;

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

Regards

Re: Sum between different date ranges

Anonymous — Fri, 04 Nov 2016 09:07:32 GMT

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

Re: Sum between different date ranges

sunny_talwar — Fri, 04 Nov 2016 16:12:53 GMT

May be try this:

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

Re: Sum between different date ranges

Anonymous — Mon, 07 Nov 2016 11:22:35 GMT

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.

Re: Sum between different date ranges

sunny_talwar — Tue, 08 Nov 2016 01:32:14 GMT

Does this look like what you wanted?

Expression used:

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

Re: Sum between different date ranges

Anonymous — Tue, 08 Nov 2016 06:37:44 GMT

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

Re: Sum between different date ranges

sunny_talwar — Tue, 08 Nov 2016 12:05:11 GMT

How about this?

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

Re: Sum between different date ranges

Anonymous — Tue, 08 Nov 2016 12:27:28 GMT

It's exacly what i need.

Thank you very much.