Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Go-Anna
Contributor III
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:

Go-Anna_0-1607115431195.png

 

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
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.

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

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

If I use this expression:

Go-Anna_0-1607177313146.png

I still get zero 😞

 

Go-Anna
Contributor III
Contributor III
Author

Go-Anna_4-1607177642769.png

 

Go-Anna
Contributor III
Contributor III
Author

 
Kushal_Chawda

@Go-Anna  try below

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

Go-Anna
Contributor III
Contributor III
Author

Thanks sooooo much Kush!!!!!

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

 

Go-Anna_0-1607191964390.png

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

MayilVahanan_0-1607225942267.png

 

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

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