Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
yanivvl0
Creator III
Creator III

How to sum sub level by dates ?

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 .

project books steps save.PNG

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

THANKS !

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

15 Replies
sunny_talwar

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

yanivvl0
Creator III
Creator III
Author

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!

sunny_talwar

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

yanivvl0
Creator III
Creator III
Author

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.

exmple prjbook_dates_sums src.PNG

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

exmple prjbook_dates_sums.PNG

hope its ok now .

sunny_talwar

Like this?

Capture.PNG

yanivvl0
Creator III
Creator III
Author

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 )

exmple prjbook_dates_sums 1.PNG   

sunny_talwar

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

yanivvl0
Creator III
Creator III
Author

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 .

exmple prjbook_dates_sums 2.PNG

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

sunny_talwar

May be this

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