Skip to main content
Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

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

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?

8 Replies
MVP

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Contributor III
Author

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

If I use this expression:

I still get zero 😞

Contributor III
Author

Contributor III
Author

@Go-Anna  try below

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

Contributor III
Author

Thanks sooooo much Kush!!!!!

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

Tricky......

MVP

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)]))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Contributor III
Author

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

Tags
Community Browser