topic Re: Set Analysis Formula with Parameter in QlikView

Set Analysis Formula with Parameter

dschuebel — Wed, 16 May 2018 12:07:19 GMT

Hi together,

I have the following Set Analysis Formula with Parameters:

fCount =Count({$< Field1 = {'$1'}, Field2 = {'$1'}, Date = {$(=$5)} > } [Value] )

If I set the values and variables directly into the formula, like

fCount =Count({$< Field1 = {'value1'}, Field2 = {'value2'}, Date = {$(=$(vD_AddXMonthsRange(-6)))} > } [Value] )

it works. But if I want to use the formula with Parameters, like

=$(f_Count(value1,value2,$(vD_AddXMonthsRange(-6))))

i got an error. Unfortuanetly I can´t find the error for this

Thanks for your help!

Re: Set Analysis Formula with Parameter

Miguel_Angel_Baeyens — Wed, 16 May 2018 15:26:01 GMT

Add single quotes to the parameter 3

=$(f_Count(value1, value2, '$(vD_AddXMonthsRange(-6))'))

Also, replace $5 by $3

Re: Set Analysis Formula with Parameter

dschuebel — Wed, 16 May 2018 15:36:52 GMT

Hey Miguel,

thanks for you reply. Unfortunately it not helps.

For better understanding, followed my formula for vD_AddXMonthsRange:

CHR(39) & '>=' & AddMonths(vD_CurrentDateInput, $1) & '<=' & vD_CurrentDateInput & CHR(39)

I used $1, $2 and $3. It was only a mistake in my post

Best regards

Re: Set Analysis Formula with Parameter

Miguel_Angel_Baeyens — Thu, 17 May 2018 07:03:08 GMT

Is the output of the AddMonths() function in the same format than the field "Date"?

Also, always use Chr(34) or " when the right side of the set modifier is a search (in this case, a range) and not a fixed value.

Re: Set Analysis Formula with Parameter

dschuebel — Thu, 17 May 2018 09:07:08 GMT

Hey Miguel,

the format from the AddMonths function is in the same Format. Please find attached my Test-Application about this problem.

I tried to use Chr(34), but I have the same problem .

Re: Set Analysis Formula with Parameter

Miguel_Angel_Baeyens — Tue, 22 May 2018 07:57:12 GMT

I would go for modifying the fSum variable to

Sum({$<
  Field1  = {'$1'},
  Field2  = {'$2'},
  Datum = {">=$(=AddMonths(Datum, $3))<=$(=Date(Max(Datum)))"}
  >}
  [Value]
)

The expansion within the expansion, while maybe possible, is returning already calculated values instead of text. This expression above in the fSum will work, though.

Re: Set Analysis Formula with Parameter

dschuebel — Tue, 22 May 2018 08:28:24 GMT

Hi Miguel, thanks for your response.

Unfortunately, if I use this formula I´m not so flexible, because I don´t want only use formulas like the AddXMonths, I want to use also some other formulas for set the Date Parameter