topic Macro in expression in QlikView

Macro in expression

Anonymous — Thu, 17 Jul 2014 10:00:48 GMT

Hi,

I made a macro to calculate the delay working days between two dates and I'd like to use it inside a chart expression...something like that:

Sum(If(ArrivalData>=DueDate,GetWorkDays (ArrivalDate, DueDate),0))/Count(NumberOfDocuments)

But the expression give me a syntax error, maybe due to the use of the macro into the expression...is that true? Is there a workaround for the problem?

Thanks.

Re: Macro in expression

alexandros17 — Thu, 17 Jul 2014 10:08:29 GMT

You can define a variable in which you repeat the logic of your macro, then use variable in expression

Re: Macro in expression

Anonymous — Thu, 17 Jul 2014 11:03:53 GMT

I did not understand the purpose to create a macro in such situation. Normally, either we create variable, or a temporary column .

If you must do something like this in chart, then you can create expressions in the chart,

first expression:

ArrivalData-DueDate

Secondexpression: Sum(If(column(1)>0, GetWorkDays (ArrivalDate, DueDate),0))/Count(NumberOfDocuments)

Something like this should help

Re: Macro in expression

Anonymous — Thu, 17 Jul 2014 11:23:02 GMT

Well, the macro is this:

Function GetWorkDays(StartDate, EndDate)

dCount = 0

For dt = StartDate To EndDate

If WeekDay(dt) <> 1 Then

dCount = dCount + 1

End If

GetWorkDays = dCount

End Function

Should someone explain to me how can I transform it in a varialble?

Thks

Re: Macro in expression

alexandros17 — Thu, 17 Jul 2014 12:00:27 GMT

Probably you need networkingdate() function instead of the variable

Re: Macro in expression

Anonymous — Thu, 17 Jul 2014 12:30:35 GMT

The NetWorkDays() Function considers the work days from Mon to Fri, contrariwise I've to consider Sat as a workable day.

This is the reason I've made the macro.

Thks.