sum monthly payments from a date interval in a pie chart

[thanhphongle]

Hello Community,

this is an example of my database

Rent ID	Rent begin	Rent end	Payments
1	01.01.12	31.12.2017	1000
1	01.01.12	31.12.2017	1000
2	01.01.13	31.12.2019	100
2	01.01.14	31.12.2019	100
3	01.01.15	31.12.2019	100
4	01.01.15	31.12.2025	10
5	01.01.16	31.12.2025	10

I want to sum up all payments ending before vFixdate and sum up all payments ending if Rent end >vFixdate AND < 01.01.2020 and sum up all payments which ends if Rent end is after 01.01.2020.

Qlikview should return me in a pie chart 3 different pieces.

First piece = sum(Payments) before vFixdate = 144000

Second piece = sum(Payments)  Rent end > vFixdate and Rend end < 01.01.2020 = 10800

Third piece = sum(Payments) Rent end > 01.01.2020 = 1440

My solution was

I create a variable called vFixdate = 01.01.2019

I picked Rent end as Dimension

My expression is:

if([Rent end]< vFixdate, sum(Payment), if([Rent end] > vFixdate AND [Rent end] < date('01.01.2020'), sum(Payment), if([Rent end]<>date('01.01.2020'), sum(Payment))))

But somehow QV returns me in a pie chart so many little pieces while i just wanna have 3 pieces which sum up all total values.

Could anyone help me out here?

[sunny_talwar · Accepted solution]

I think you would need a calculated dimension here:

If([Rent end] < vFixdate, 'First Piece',

If([Rent end] < Date('01.01.2020'), 'Second Piece', 'Third Piece'))

and then use Sum(Payment) as your expression

[kaushiknsolanki]

Hi,

You should have dimension and expression as below.

Dimension

if([Rent end]< vFixdate, 'Before ' &vFixDate , if([Rent end] > vFixdate AND [Rent end] < date('01.01.2020'), 'Between ' &vFixDate  &' and ' & date('01.01.2020') , if([Rent end]<>date('01.01.2020'), 'After ' &date('01.01.2020') )))

And your expression should be

Sum(Payment)

Regards,

Kaushik Solanki

[sunny_talwar]

Attaching a sample based on your provided data