Tricky Sums in Qlik Sense

[AnnaEKinch]

Hi!

I have users who are charged for IT-services in periods (months) while they are "active" and in some cases after they no longer are employed and are therefore "inactive".

If a user becomes inactive and they  should no longer be charged for IT-services.

I have a CostsAfterLastDayofEmploymentFlag on users.

I want to show a sum (in two different pivot tables below) for IT-services still being debited in the "latest" period.

Using sum({< CostsAfterLastDayofEmpoymentFlag={'Y'},[DataType]={'Actual'}>}[Price (SEK)]) I can see the total that the user has been debited after they became inactive.

I have user X who was active until 202001 (jan), in 202002 (feb) they become inactive. 

User X has been debited a number of services to a total of 18 SEK since she became inactive. I only want to see the 13 SEK total of IT-services still being debited.

IT-Service A - 6  SEK for all periods since she became inactive  period 202002-202102 (feb last year-feb this year ie still being debited)

IT-Service B - 5 SEK for periods  202002-202011 (feb-nov last year no longer being debited after nov)

IT-Service C - 5 SEK for all periods since she became inactive periods 202002-202102 (feb last year-feb this year ie still being debited)

IT-Service D - 2 SEK for all periods since she became inactive periods 202002-202102 (feb last year-feb this year ie still being debited)

So the total since she became inactive is 18 SEK, but only 13 SEK are still being debited.

I only want to see the total of IT-services which are still being debited in the latest period.

So any suggestions as to how I can show only the sum IT-costs still running?

I tried this:

I made a variable vMaxPeriod variable is: =Max({<[DataType]={'Actual'},[Price (SEK)]={">0"} >} [Period])

Then I used the following formula:

If(Sum({$<Period={'$(=$(vMaxPeriod))'},[DataType]={'Actual'},CostsAfterLastDayofEmpoymentFlag={'Y'}>}[Price (SEK)])<>0,

sum({< CostsAfterLastDayofEmpoymentFlag={'Y'},[DataType]={'Actual'}>}[Price (SEK)]),null())

But returns the total of all IT-services debited on the user level after they became inactive 18 SEK but I want to see 13 SEK: