topic Re: Help in expression in App Development

Help in expression

ali_hijazi — Tue, 18 Jun 2019 08:28:02 GMT

Hello
I'm working on a pivot table

got the following expression that calculates the average man-days

avg
(
{
<
MONTH_YEAR,YEAR,MONTH,[Fiscal Month],[Fiscal Quarter],[Fiscal Year],
MONTH_NUM={">= $(vL.MinMonthYear_Future) <=$(vL.MaxMonthYear)"}
,MAN_DAYS_TYPE={"planned"}
>
}
aggr
(
sum
(
{
<
MONTH_YEAR,YEAR,MONTH,[Fiscal Month],[Fiscal Quarter],[Fiscal Year],
MONTH_NUM={">= $(vL.MinMonthYear_Future) <=$(vL.MaxMonthYear)"}
,MAN_DAYS_TYPE={"planned"}
>
}
MAN_DAYS
)
,MONTH_YEAR,[Project Type], [Project Name],[Primary Resource Stream],[Resource Full Name],[Assignment Comment]
)
)

Now instead of the $(vL.MaxMonthYear) ,I want to get the value of another cell whose formula is :

max
(
{
<
MONTH_YEAR,YEAR,MONTH,[Fiscal Month],[Fiscal Quarter],[Fiscal Year],
MAN_DAYS_TYPE={"*"}
>
}
MONTH_YEAR
)

how would my expression be as I'm unable to find out

Re: Help in expression

felipedl — Mon, 17 Jun 2019 13:45:47 GMT

That's a complex expression to verify and debug without an app or data attached.
You have variables, an imense set analysis and aggregation that only the person that wrote it will understand.

Attach an app and we'd likely be able to help on it.

Re: Help in expression

Oleg_Troyansky — Mon, 17 Jun 2019 13:58:36 GMT

The simple answer is this:

In order to replace a variable with a formula in a $-sign expansion, add an equal sign "=" in front of your formula:

replace $(variable) with $(=formula)

Now, if that doesn't work, then I'd also ask you to post a sample of your app with the formula, and someone will try to take a look.

Best,

Oleg Troyansky

Check out my sessions on AGGR, Set Analysis, and Performance at the Masters Summit for Qlik - coming to Washington, DC, and Amsterdam, Netherlands this fall!

Re: Help in expression

ali_hijazi — Tue, 18 Jun 2019 08:30:40 GMT

check attached sample

go to sheet Avg Data for Man-days
in the pivot table the last column is the calculated maximum month of allocation

in the previous column I want to calculate avg man-days between Jun2019 and the calculated column (last one) which is the one in concern

Re: Help in expression

Oleg_Troyansky — Tue, 18 Jun 2019 15:51:30 GMT

The App is huge, and it has Section Access, which prevents it from opening. Kindly make a smaller example with no section access ...