topic Re: How to calculate accumulated value in QlikView

How to calculate accumulated value

Tue, 12 Aug 2014 08:59:55 GMT

Hello every one.

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

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

2^nd 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.

Re: How to calculate accumulated value

Miguel_Angel_Baeyens — Tue, 12 Aug 2014 09:05:38 GMT

Hi Omar,

Try adding a set analysis like:

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

Miguel

Re: How to calculate accumulated value

syukyo_zhu — Tue, 12 Aug 2014 09:05:45 GMT

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

Re: How to calculate accumulated value

tresB — Tue, 12 Aug 2014 09:10:17 GMT

Like this?

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

Re: How to calculate accumulated value

datanibbler — Tue, 12 Aug 2014 09:11:52 GMT

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.

Re: How to calculate accumulated value

Miguel_Angel_Baeyens — Tue, 12 Aug 2014 09:24:57 GMT

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--part-i) -among many others!- about QlikView in general.

Miguel

Re: How to calculate accumulated value

datanibbler — Tue, 12 Aug 2014 09:32:59 GMT

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

Re: How to calculate accumulated value

Tue, 12 Aug 2014 11:05:50 GMT

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.

Re: How to calculate accumulated value

Tue, 12 Aug 2014 12:43:38 GMT

I changed the formula to

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

but it's not showing any value.?

Re: How to calculate accumulated value

Tue, 12 Aug 2014 13:35:24 GMT

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