Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hansdevr
Contributor II

How do I create a table from variables to generate totals per month?

I have the following table (simplified):

 

ID

Start_date

End_date

Amount_In_€

10001

1-6-2015

4-6-2015

5000

10001

5-6-2015

9-6-2015

4000

10001

10-6-2015

31-8-2015

2000

20001

1-4-2015

3-6-2015

7500

20001

4-6-2015

1-7-2015

4000

20001

2-7-2015

31-8-2015

0

30001

1-3-2015

17-3-2015

10000

30001

18-3-2015

11-4-2015

6200

30001

12-4-2015

31-8-2015

4000

 

 

Using this expression in a text box, allows me to sum the amounts for any given date (vDate) over the entire range of records in my table (imagine many thousands of ID’s).

 

=Money(sum( if( Start_date<= vDate AND End_date>= vDate, [Amount_in_€]) ))

 

(which means that only a portion of the table is totalized, i.e. the records that meet the expression)

 

 

Now, what I’d like to do, is show the progression of the total from the expression over a range of dates (oldest Start_date up to today) in a line graph.

 

 

Thinking about this, I guess I will have to generate a new table, that would look something like this:

 

 

Date

Total_in_€

1-4-2015

2.654.290

2-4-2015

22.380.411

3-4-2015

23.015.766

4-4-2015

24.844.321

 

 

Could anyone help me make the script that builds this new table from the original one?

 

Many thanks in advance!

 

Tags (2)
Community Browser