Qlik Sense App Development

Announcements
Partner

Help in expression

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

I can walk on water when it freezes
4 Replies
Partner

Re: Help in expression

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.
MVP & Luminary

Re: Help in expression

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!

Partner

Re: Help in expression

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

I can walk on water when it freezes
Highlighted
MVP & Luminary

Re: Help in expression

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