Sum of values in straight table and KPI based on date expression

[Go-Anna]

Hi!

I can't get my head around this!!!

I have a table:

For when ExEmployee = YES, I want to calculate the measure sum of [Actual YTD 2020 (kSEK)]  when Period >[Last day of Employment]. (One may have to convert the Last day of Employment to a YYYYMM format....but not sure)

I would also like to have this formula as a KPI.

Would anybody please like to help me with this?

[MayilVahanan]

HI @Go-Anna 

Try like below

Sum({<ExEmployee = {'YES'}, Period ={"=Period >=Date([Last day of Employment],'YYYYMM')"}>}[Actual YTD 2020 (kSEK)])

If its not works, please send the sample data.

[Go-Anna]

Hi Mayil Vahanan! Thank you so much for trying to give me a solution!

If I use this expression:

I still get zero 😞

[Go-Anna]

[Kushal_Chawda]

@Go-Anna  try below

Sum({<ExEmployee = {'YES'}>} if(floor(Period) >=floor([Last day of Employment]),Actual YTD 2020 (kSEK)]))

[Go-Anna]

Thanks sooooo much Kush!!!!!

Hmmm...tried both of these.....neither worked

Tricky......

[MayilVahanan]

Hi @Go-Anna 

Try like below

LOAD ExEmployee,
Date(Date#([Last day of Employment],'YYYY-MM-DD'),'YYYY-MM-DD') as [Last day of Employment],
MonthEnd(MakeDate(Left(Period,4), Right(Period,2))) as Period,
"Sum([Price (SEK)])_x000D_" as [Price (SEK)]
FROM
[D:\Qlik\personnal\ExEmployee Price after Last day of Employment.xlsx]
(ooxml, embedded labels, table is Sheet1);

Front end:

Sum({<ExEmployee = {'YES'}>}If(Period >= [Last day of Employment], [Price (SEK)]))

[Go-Anna]

Thank you, I will try this, definitely works for you. Thank you so much 🙏