topic Re: How to sum sub level by dates ? in QlikView

How to sum sub level by dates ?

yanivvl0 — Sun, 26 Mar 2017 10:57:05 GMT

hi to all ,

i have projects , and in a project can be some books , each book has amount of unitss , and each project make savings of units.

i need to sum for a save-date of project the saved units until the save-date , and also the book-units that have any step until the save-date.

for example in this project for save-date 1/1/2016 there is 2 saved units , but no book units bcause any of the tree books has a step beffore 1/1/2016 .

for example in this project for save-date 1/6/2016 there is 15 saved units , and 104 book units bcause all of the tree books has a step beffore 1/6/2016 .

i need to see project - save-date - saved units - book units .

THANKS !

Re: How to sum sub level by dates ?

sunny_talwar — Sun, 26 Mar 2017 12:10:23 GMT

Would you be able to share a sample to play around with? and it would be helpful to know the numeric output you are looking to get here

Re: How to sum sub level by dates ?

yanivvl0 — Sun, 26 Mar 2017 13:33:02 GMT

hi Sunny , here it is ,

As i said ,

i need to see ( pivot )

dimension : project , save_date .

measures : sum of saved-units until save_date , sum of book-units than have any step until save_date.

THANKS!

Re: How to sum sub level by dates ?

sunny_talwar — Sun, 26 Mar 2017 23:04:33 GMT

Like I asked, I won't know what the numeric output you are looking to get.... If you are not sure what is the numeric output needed, then pick one example and explain the logic of what is needed for a single row so that we can deduce what is needed here

Re: How to sum sub level by dates ?

yanivvl0 — Mon, 27 Mar 2017 06:17:41 GMT

hi Sunny , sorry for not being clear . in this example you can see that only from the 1/8/2010 i have 3 saved units but no book from the 6 books had any step until this date . at the next record you can see that at the 1/9/2010 i have another 14 saved units so the sum (save units) is 3+14=17 , And all the 6 books had a step at the 26/8/2010 so at the 1/9/2010 the sum (book units) is 6 book * 6 units for each = 36.(- if there was only 2 books with steps in this date so it 2*6=12 )

at the next record you can see that at the 1/10/2010 there is no chage - no saved units , and all the books have steps from the prev month.

Note - sum (save units) and sum (book units) are Aggregated values and only increase during time.

A desired result - ( pivot or any thing else .. )

hope its ok now .

Re: How to sum sub level by dates ?

sunny_talwar — Mon, 27 Mar 2017 08:58:44 GMT

Like this?

Re: How to sum sub level by dates ?

yanivvl0 — Mon, 27 Mar 2017 09:28:28 GMT

almost perfect ,

in this project there are no steps for any book at the 1/8/2010 or before , so sum (book units) have to be 0 at this date.

( sum (save units) is OK )

Re: How to sum sub level by dates ?

sunny_talwar — Mon, 27 Mar 2017 09:33:39 GMT

Check now... but check it with other selections to make sure that this is what you wanted

Re: How to sum sub level by dates ?

yanivvl0 — Mon, 27 Mar 2017 10:40:35 GMT

sorry Sunny , i delete from source the first 2 steps for book 76623 so 6 units that belonge to it do not count until 3/10/2010 .

i need to see at the 1/9/2010 the value 30 for sum (book units) , but i see 36 .

BIG THANKS for trying to help me , I'm stuck ....

Re: How to sum sub level by dates ?

sunny_talwar — Mon, 27 Mar 2017 11:03:30 GMT

May be this

Sum(Aggr(If([save date] >= Min([step date]), Sum([book units])), project, [save date], book))

Re: How to sum sub level by dates ?

yanivvl0 — Mon, 27 Mar 2017 11:22:29 GMT

HI Sunny , again I highly appreciate the professional help you give !!

Re: How to sum sub level by dates ?

sunny_talwar — Mon, 27 Mar 2017 11:23:38 GMT

No problem my friend...

Re: How to sum sub level by dates ?

yanivvl0 — Mon, 27 Mar 2017 11:45:51 GMT

HI Sunny , i found a little err in sum (save units) , at the yellow marks the aggrigation Resetting :

Re: How to sum sub level by dates ?

sunny_talwar — Mon, 27 Mar 2017 11:47:34 GMT

I am not seeing this

Re: How to sum sub level by dates ?

yanivvl0 — Mon, 27 Mar 2017 12:32:35 GMT

my mistake ,,

Re: How to sum sub level by dates ?

sunny_talwar — Mon, 27 Mar 2017 12:33:24 GMT

What was wrong?