Help in expression

[ali_hijazi]

Hello
I have a pivot table with several expressions
I will show you 2 expressions 1 and 2 written in two different ways but they return the same correct result

expression 1 is not distributed on any dimension
while expression 2 is distributed on any dimension

but expression 2 is very slow when used with the remaining expressions in the pivot table
while expression 1 is much faster

Expression1:
{
$
-<[Parent PRoject Portfolio Level 1 ID]={1},[Portfolio Level 1 ID]={18}>
-<[Parent PRoject Portfolio Level 1 ID]={1},[Portfolio Level 3 ID]={37}>
}
sum(SPENT_MANDAYS)

Expression 2:

(
sum(SPENT_MANDAYS)
-
{<[Parent PRoject Portfolio Level 1 ID]={1},[Portfolio Level 1 ID]={18}>}
sum(SPENT_MANDAYS)
-
{<[Parent PRoject Portfolio Level 1 ID]={1},[Portfolio Level 3 ID]={37}>}
sum(SPENT_MANDAYS)
)

kindly advise on why expression 1 is alwys under null scenario type

[robert_mika]

E1 is per chart, E2 is per row

E1 has one aggr, E2 has 3 (the reason of slow execution).

I suggest to do the calculation in the script if you need performance improvement.

[ali_hijazi]

E1 is fast but comes under null scenario though the result is the same (I mean the number)

[Daniel_Castella]

Hi @ali_hijazi 

I don't see any error in your expressions. Both are equivalent. Hence, I'm not sure why the first one returns a null. Could it be possible for you to attach the app (or a mock app) with this issue in order I can check it on my end?

Kind Regards

Daniel

[stevedark]

Hi @ali_hijazi 

You need to be careful when doing set anlaysis outside of expressions that the scope of the set analysis is as you expect. Adding extra brackets can often help.

Personally, unless I have a good reason not to, I will include any set analysis statement inside the expression, for example:

sum(
{
$
-<[Parent PRoject Portfolio Level 1 ID]={1},[Portfolio Level 1 ID]={18}>
-<[Parent PRoject Portfolio Level 1 ID]={1},[Portfolio Level 3 ID]={37}>
}
SPENT_MANDAYS)

If you don't exclude the values with set analysis (i.e. just sum(SPENT_MANDAYS)), where does the value appear?

Steve

[ali_hijazi]

Hello @stevedark 
I if I don't exclude the values are located under the correct scenario and year values

when I exclude in set analysis:

the remaining projects come under null year and scenario
but if I remove the project which is supposed to be excluded in set analysis i.e. the one with portfolio_leve_3_id = 18, the remaining ones are placed under the correct year and scenario

regarding the set analysis
the behavior is the same whether you include them inside the aggregate function like sum,max,... or put them outside
this approach is good for reusing variables with additional conditons

suppose you have a variable v1 defined as sum({<currency={[eur]}>}amount)
you can reuse it as follows:

{<product={[p1],[p2]}>}
$(v1)

[ali_hijazi]

wish this was qlikview via which we could reduce the document with a single button click
I will try to reduce the app manually and share it with  you

[stevedark]

Hi @ali_hijazi 

That is very strange behaviour. What is the data model behind this app? Which fields are in the same table and which are in different tables?

If the level 1 ID and level 3 ID are in the same table, I would try creating a composite key from them in the load, like this:

[Parent PRoject Portfolio Level 1 ID] & '-' & [Portfolio Level 1 ID] as PortfolioID,

And then use this in the set analysis:

sum({<PortfolioID-={'1-18','1-37'}>}SPENT_MANDAYS)

If the two ID are not in the same source table this will not be so easy, you might need to use ApplyMap to attach the IDs in order to create the key.

I'm well aware of how the outer set analysis is useful, and I'm very impressed that it even works when you put the outer set analysis in front of a master measure, like this:

{<Region*={'North'}>} [Master Measure Name]

As I mentioned before though, the scope of each bit of set analysis is unclear, unless you bracket things, thus:

({<Region*={'North'}>} [Master Measure Name])

I'm probably just a bit sceptical of outer set analysis, as for the majority of my career in Qlik it was not available to me. I've not hit many problems when I have used it, and I have used it quite a bit now, but old habits die hard.

Cheers,
Steve

[ali_hijazi]

Hi @stevedark 
all the fields in the screenshot above are in the same dimension table called Project_HISTORY (PARENT_PROJECT, PROJECT_NAME, PORTFOLIO 1 ID, PORTFOLIO 2 ID) except for the year and the scenario

the thing is that the result number is correct but falls under null dimensions outside the Project_History table when the project to be eliminated is in the current selection set of data

the dimension table Project is linked to the fact table via a field named PRJH_KEY 
next to the PRJH_KEY are two fields:
MONTH_KEY which links to the Calendar table where the YEAR field resides
SCNR_TYPE_KEY which links to the scenario type table where the Scenario Type resides

and the SPENT_MANDAYS (which I'm summing up) is in the fact table