topic Re: Set Analysis Problem. in QlikView

Set Analysis Problem.

Thu, 17 Jul 2014 05:31:15 GMT

Hi There,

I really want to write this formula but not sure how...

I want all the entries for the current month

This works:

Sum({$<KeyDate = {">=$(=Only(AsAtMTD))<=$(=Only(AsAtMonthEnd))"}>} BudgetNonAccommodationCleaningDailyHours)

But instead of AsAtMonthEnd I want this value which is the latest timesheet entry which is the result of the next expression.

Max({$<KeyDate = {">=$(=Only(AsAtMTD))<=$(=Only(AsAtMonthEnd))"}, TimesheetHoursWorked={">0"}>} KeyDate)

How could I write the expression to achieve this?

e.g. Which doesn't work.

Sum({$<KeyDate = {">=$(=Only(AsAtMTD))"}, KeyDate = P({Max({$<KeyDate = {">=$(=Only(AsAtMTD))<=$(=Only(AsAtMonthEnd))"}, TimesheetHoursWorked={">0"}>} KeyDate)>} )>} BudgetNonAccommodationCleaningDailyHours)

Thanks in advance

Luke

Re: Set Analysis Problem.

Thu, 17 Jul 2014 05:39:03 GMT

Hi,

Store the value of this expression in a variable

vLatestTimesheetEntry = Max({$<KeyDate = {">=$(=Only(AsAtMTD))<=$(=Only(AsAtMonthEnd))"}, TimesheetHoursWorked={">0"}>} KeyDate)

and then use this expression in set analysis

Sum({$<KeyDate = {">=$(=Only(AsAtMTD))<=$(vLatestTimesheetEntry))"}>}

Re: Set Analysis Problem.

VishalWaghole — Thu, 17 Jul 2014 05:42:42 GMT

Hi Like,

Sum({<KeyDate={">=$(=Num(MonthStart(Max(KeyDate))))<=$(=Max(KeyDate))"}, TimesheetHoursWorked={">0"} >} BudgetNonAccommodationCleaningDailyHours)

Hope this expression work for you.

-- Regards,

Vishal Waghole