Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need Help with Syntax

Hi,

I have a problem in which I am trying to show the comparison between current month's total work hours logged by employee vs the last month. (I am using KPI for this purpose)

My table has date as shown, all sequentially:

img.JPG

I basically want to sum the total hours for the last month and current month and show that as a KPI.

I tried Sum( {“>=$(=Min(Date))<=$(=Max(Date))”} } EmpHrs) but its not giving any result.

Any inputs shall be greatly appreciated.

Thanks

1 Solution

Accepted Solutions
chriscammers
Partner - Specialist
Partner - Specialist

Current Month Hours = =sum(   {<Date = {">=$(=MonthStart(Max(Date))) <=$(=MonthEnd(Max(Date))) "}>}     EmpHrs)


Previous Month Hours = =sum(   {<Date = {">=$(=MonthStart(Max(Date),-1)) <=$(=MonthEnd(Max(Date),-1)) "}>}     EmpHrs)

View solution in original post

7 Replies
its_anandrjs

Try this ways

=sum(   {<Date = {">=$(=Min(Date)) <=$(=Max(Date)) "}>}     EmpHrs)

Regards

Anand

Not applicable
Author

Hi Anand,

This works but how do I get the hours for the next month.

As in If I use the same formula for Current month it gives me the same value. I have used this right now in last month's formula.

I wanted something like this:

img.JPG

Thanks

chriscammers
Partner - Specialist
Partner - Specialist

Current Month Hours = =sum(   {<Date = {">=$(=MonthStart(Max(Date))) <=$(=MonthEnd(Max(Date))) "}>}     EmpHrs)


Previous Month Hours = =sum(   {<Date = {">=$(=MonthStart(Max(Date),-1)) <=$(=MonthEnd(Max(Date),-1)) "}>}     EmpHrs)

its_anandrjs

In your load script add NUM month field to get Num Months that used on expression

Ex:-

Load

Num(Month(Datefield)) as NumMonth

From Location;

Then in expression for current month

=sum(  {<Date = {">=$(=Min(Date)) <=$(=Max(Date)) "}, NumMonth = {"$(=Num(Month(MAX(Date ))))"} >}  EmpHrs)

And for previous month

=sum(  {<Date = {">=$(=Min(Date)) <=$(=Max(Date)) "}, NumMonth = {"$(=Num(Month(MAX(Date )))-1) "} >}  EmpHrs)

Not applicable
Author

Thanks Chris.

This worked well.

Not applicable
Author

Thank you for the response Anand.

This also worked for me.

its_anandrjs

Thanks please close the thread with mark appropriate answer correct or helpful.

Regards

Anand