topic Re: Drill down group set analysis in QlikView

Drill down group set analysis

salezian — Mon, 13 Nov 2017 12:37:06 GMT

Dear Qlik Developers,

I have following problem to solve: I have drill down group let say consisting of following dimensions A->B->C->

then I have expression sum(sales). On the dashboard user should see 2 values

1. for currently selected level

2. reference always for data 1 level up

For example when users is on level C then I need to calculate 2 expressions sum(sales) and sum({<,C=>}sales)

I'd like to calculate formula like

if(getCurrentField(Group)='B','{<B=>}', if(getCurrentField(Group)=C,'{<C=>} '...

When I'm passing this variable to expression I got error invalid set identifier.

I'd grateful for any clue

Re: Drill down group set analysis

sunny_talwar — Mon, 13 Nov 2017 12:40:15 GMT

Did you try clicking okay to see if you get the result you expected? Syntax editor, at times, show error even when the expression is correct.... I would suggest you to check the output and ignore the error message for a little bit

Re: Drill down group set analysis

Anil_Babu_Samineni — Mon, 13 Nov 2017 12:40:24 GMT

Perhaps this?

If(GetCurrentField('[Group]') = 'B', Sum({<B=>} Sales), If(GetCurrentField('[Grup]') = 'C', Sum({<C=>} Sales)))

Re: Drill down group set analysis

Peter_Cammaert — Mon, 13 Nov 2017 12:54:05 GMT

You can't use QlikView expressions to dynamically assemble pieces of a set analysis specification. Because that would mean that some parts of the same expression must be evaluated before the remainder is evaluated, and QlikView has no idea at all about what should come first. A Set Analysis specification is a fixed string in which you are allowed to replace parts with $-sign substitution before any evaluation happens. Afterwards, the evaluation is performed in one stroke.

For example, this will not work:

=Sum(IF (UseSetAnalysis,{<Field={1,2,3}>}) SalesAmount) //Conditional set analysis?

Re: Drill down group set analysis

salezian — Mon, 13 Nov 2017 14:00:09 GMT

Thanks, I came to the same conclusion. I find out following solution

varCompareSet =if(getCurrentField(Group),'A',if(getCurrentField(Group)='B','A' ....

then in expression

sum({<$(vCompareSet)=>}

The trick is to evaluate variable varCompareSet before expression is evaluated. As Peter pointed out set analysis must be literal string.