Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate accumulated value

Hello every one.

I have attached my data sample. What I need based on the
date selection. To show 2 calculated column.

   

1st one based on selected date range. And this is
easy done.

 

2nd column bases on selected date I need to get
from day 1  up to Max (selected date)

Accumulated.

For the same item?

   

This is my problem. Any help.

1 Solution

Accepted Solutions
Not applicable
Author

after many trial and with help of Miguel,
finally it's resolved.

View solution in original post

9 Replies
Miguel_Angel_Baeyens

Hi Omar,

Try adding a set analysis like:

Sum({< TrDate = {"<=$(=Date(MonthStart(TrDate), 'DD-MM-YYYY'))"} >} Quantity * Cost)

Miguel

syukyo_zhu
Creator III
Creator III

try this: sum({< Date={"<=$(=vDateJ_1)"},Année={$(vMaxYear)}>}yourmesure)

tresesco
MVP
MVP

Like this?

=sum ({<TrDate={"<=$(=Max(TrDate))"}>}Quantity * Cost)

datanibbler
Champion
Champion

Hi omar,

I haven't downloaded your sample (would take ages with our connection 😉

Generally said, though, you will certainly need Set_analysis.

One of the easiest features of set_analysis (it can get nigh arbitrarily complex) is to disregard any

user_selections to begin  with - and then build an expression based on that one selection (using the GETFIELDSELECTIONS([field])l function.

It should then look sth like this:

>>>  SUM({1<date = {$(=max(date))}>} [field]) <<<

That is, the parameter 1 inside the curvy_brackets tells QV to disregard all selections to begin with, and within the pointed_brackets, you equal one existing field to a certain value, which, in your case, would be an expression. You can look up the details of the syntax in the help_file, in the excellent "QlikView 11 for Developers" - or you can ask here.

HTH

Best regards,

DataNibbler

P.S.: Sorry, no offense meant to anyone - but I just think that explaining how to do sth. works better than typing up a (albeit working) formula here - all those who don't want to do this over and over again, ask the mods to consider creating a Wiki as I have done.

Miguel_Angel_Baeyens

Hey DataNibbler,

I also agree that the Qlik Community needs to be a fast place. I used to try and explain all things, resulting in quite long posts or just mere references to the Manual, and I admit I gave up. After joining Qlik I tried encouraging people to do what I thought it is best, then I realized I cannot push people to do things my way. That's one of the advantages of Qlik Community: you can have both detailed explanations in replies or blog posts, or quick answers.

I still try to do that when I see there is something wrong or very confusing, but for the time being I don't have the time to have my own blog (one internal plus one external).

We are lucky to have this eighteen resources (http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/22/the-qlikosphere-external-resources--pa...) -among many others!- about QlikView in general.

Miguel

datanibbler
Champion
Champion


Hi Miguel,

agreed. The blogs are a great source of information also. Also, a Wiki is not a panacea - many users tend to have a look in Wikis, then decide that an article doesn't meet their exact requirements and scenario, and go back to the forum anyway to ask - I readily admit that I'm no exception.

Best regards,

DataNibbler

Not applicable
Author

Hello Miguel,

this is pretty much close to what is need, but still not giving the correct value.

if I select 2nd Sept 2014 with Item Number 28508

the accumulated value should be 1-9-2014 + 2-9-2014 days value

119.25 + 79.5 = 198.75

so its still not showing correct value.


Not applicable
Author

I changed the formula to

=Sum({< TrDate = {"<=$(=Min({$}TrDate), 'DD-MM-YYYY'))"} >} {$} Quantity * Cost)

but it's not showing any value.?

Not applicable
Author

after many trial and with help of Miguel,
finally it's resolved.