topic Re: Set Modifier including in Master Measure in QlikView

Set Modifier including in Master Measure

Anonymous — Wed, 13 Jun 2018 16:01:37 GMT

Hi Guru's,

I have 2 Set Modifiers stored in variables. iam calculating YTD value using below variables

1. vJ_DateModifier =

sum({<

JType={'Value'},

FactType={'Essbase_J'},

Flag_J_Refinement={'1'},

>} J_Refinement_Data)

2.vSetYTDModifier =

[FactDate]=,[Day]=,[Weekday]=,[Year]=,[Month]=,[MonthName]=,[Quarter]=,[_DateSerial]=,[_MonthSerial]=,

[_QuarterSerial]=,[_WeekSerial]=,[_Qvc.Calendar_DateIsFuture]=,[Year-Month]=,[Year-Quarter]=,

[_DateSerial] = {"<=$(=Max({1<$(_f_DateIsFuture)={0}

,_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[_DateSerial]))"},

[Year] = {"$(=Max({1<$(_f_DateIsFuture)={0},_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[Year]))"}

QTD calculation: $(vJ_DateModifier2)(vSetYTDModifier))

using above expression in Master measure to calculate the YTD

If i use like below its showing correct value but if i use like $(vJ_DateModifier2)(vSetYTDModifier)) in master items its showing wrong value.how to simplyfy the YTD calculation using above two variables.

working script:

sum({<

JType={'Value'},

[FactDate]=,[Day]=,[Weekday]=,[Year]=,[Month]=,[MonthName]=,[Quarter]=,[_DateSerial]=,[_MonthSerial]=,

[_QuarterSerial]=,[_WeekSerial]=,[_Qvc.Calendar_DateIsFuture]=,[Year-Month]=,[Year-Quarter]=,

[_DateSerial] = {"<=$(=Max({1<$(_f_DateIsFuture)={0}

,_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[_DateSerial]))"},

[Year] = {"$(=Max({1<$(_f_DateIsFuture)={0},_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[Year]))"},

FactType={'Essbase_J'},

Flag_J_Refinement={'1'},

>} J_Refinement_Data)

Thanks,

Raj

Re: Set Modifier including in Master Measure

rubenmarin — Wed, 13 Jun 2018 16:35:26 GMT

Hi Raj, that's because each comma will be considered as a parameter separator, it will need additional $'s:

sum({<

JType={'Value'},

FactType={'Essbase_J'},

Flag_J_Refinement={'1'},

$1, $2, $3, $4... // Fill to cover all parameters

>} J_Refinement_Data)

Re: Set Modifier including in Master Measure

Anonymous — Thu, 14 Jun 2018 07:46:09 GMT

Hi Ruben ,

Thanks for your time,i have 17 comma's in my set Modifier variable. do i need to use $1,$2,........$17 in my $(vJ_DateModifier?

2.vSetYTDModifier =

[FactDate]=,[Day]=,[Weekday]=,[Year]=,[Month]=,[MonthName]=,[Quarter]=,[_DateSerial]=,[_MonthSerial]=,

[_QuarterSerial]=,[_WeekSerial]=,[_Qvc.Calendar_DateIsFuture]=,[Year-Month]=,[Year-Quarter]=,

[_DateSerial] = {"<=$(=Max({1<$(_f_DateIsFuture)={0}

,_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[_DateSerial]))"},

[Year] = {"$(=Max({1<$(_f_DateIsFuture)={0},_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[Year]))"}

Re: Set Modifier including in Master Measure

rubenmarin — Thu, 14 Jun 2018 08:53:58 GMT

When you do the call $(vJ_DateModifier2)(vSetYTDModifier)), vSetModifier is:

$(vJ_DateModifier2([FactDate]=,[Day]=,[Weekday]=,[Year]=,[Month]=,[MonthNa...))

This are interpreted as different parameters, so vJ_DateModifier2 needs to have a place to store them. $1 for the first parameter, $2 for the second...