sum of amount in a specific period

[qlikviewuser20]

Hi everybody,

What i want to do is to calculate a sum in a specific period.

So for example, if i choose a period between 03/2016 and 04/2016, i have to calculate an expression:

(sum(amount in 03/2016)*number of days of march+ sum(amount in 04/2016))*number of days of april/ number of days of the period

any help ?

Thank you very much

[swuehl]

How does your data model look like? Are you using a kind of master calendar?

Can you upload a small sample QVW?

[maniram23]

Hi,

if yo know the exact date. than you can write like this

your date should be in MM/YYYY formet.

sum({<date={'3/2016','4/2016'}>}Amount)

[swuehl]

This would be a very simple example of how you can do it:

SET DateFormat = 'MM/DD/YYYY';

AMOUNT:
LOAD *, Monthname(Monthstart) as Monthname, Day(MonthEnd(Monthstart)) as NoOfDaysInMonth INLINE [
Monthstart, Amount
03/01/2016, 1000
04/01/2016,1100
05/01/2016, 900
];

Then, e.g. in a straight table or text box, use

=Sum(Amount*NoOfDaysInMonth) / Sum(NoOfDaysInMonth)

Monthname	Sum(Amount*NoOfDaysInMonth) / Sum(NoOfDaysInMonth)
	998,91304347826
Mar 2016	1000
Apr 2016	1100
May 2016	900

This basically works with a record per month. You can use a master calendar instead of creating the NoOfDaysInMonth in the fact table if you have more complex facts.

[qlikviewuser20]

Thank you for your reply. But that is not exactly what i want.

Here is my data:

Id Amount month year

1  1000       march 2016

2  2000       april     2016

3  3000       jan.      2015

4  4000       dec.     2014

Etc

What i did : i created two varibale:

vMinDate= min (makedate (year,month,1))

vMaxDate= max (makedate (year,month,1))

After that i added two calendar object : from (vMinDate) to (vMaxDate)

Then a use triggers so when i choose a vMinDate or vMAxDAte , year and month will be selected

After that i want to display a table with the id and the sum of the amount so when i choose 01/03/2016 as vMinDate and 01/04/2016 as vMaxDate , the expression will be like that  ((1000*31)+(2000*30))/61

[swuehl · Accepted solution]

I think that's pretty much what I showed above, isn't it?

If your trigger makes selections in your year and month fields (i.e. selecting the range of calendar values you are interesting in), above should basically work.

If you have problems to make it work, then please post a small sample QVW.

[qlikviewuser20]

Thank you very much, it works perfectly