topic Re: collapse function in expression in QlikView

collapse function in expression

Wed, 25 Nov 2020 16:16:04 GMT

I am a new qlik view developer and I wonder if there is a way how to give a value in the expression when a dimension is collapsed.

For example, if we have 3 classes (a, b, c), when collapsed against it should appear value (6).

Dimension	Expression
a	1
b	5
c	4

when collapsed it has to show:

Dimension	Expression
abc	6

Thanks in advance and regards,

Stoyan

Re: collapse function in expression

sunny_talwar — Thu, 05 May 2016 14:54:05 GMT

How did you get 6 for abc? It is clearly not the sum of 1+5+4, right? In addition, what is the dimension you are using and what is the expression? Would you be able to elaborate?

Re: collapse function in expression

Mon, 09 May 2016 05:37:15 GMT

Hi Sunny,

Yes, 6 has nothing to do with the sum of the above, it is just a different value. The dimension is cyclic group and the expression I need to build and let's say I will use variable for it.

Thanks

Re: collapse function in expression

sunny_talwar — Mon, 09 May 2016 16:48:45 GMT

So how did you come with 6? How does the two table provided above related to each other?

Re: collapse function in expression

Thu, 12 May 2016 11:49:22 GMT

Hi Sunny,

The idea is that I have three regions - east, west and central. This region I use in the dimensions as column in the pivot table. Against these regions I have some actuals sales performed and I have targets for each region, as well as global target. I am taking the targets by region with variables (against each region I have some target shown, defined by the variable). However, I need to say to QV when the "Region" dimension is collapsed (i.e. we have the global actuals for the sales) to show the global target, which is a different figure.

Hope that this helps.

Thanks,

Stoyan

Re: collapse function in expression

marcus_sommer — Thu, 12 May 2016 13:17:37 GMT

Maybe you used a dimension group "structure" with the fields:

global --> region --> area --> ...

and then you queried which value is displayed, like:

pick(match(getcurrentfield(structure), 'global', 'region', ...),

sum(TargetGlobal),

sum(TargetRegion),

...)

- Marcus

Re: collapse function in expression

Tue, 17 May 2016 05:41:00 GMT

Hi Marcus,

Thanks for the idea, but it does not work in this case. It has to be using somehow the collapse function. Any ideas on this?

Thanks and regards,

Stoyan

Re: collapse function in expression

marcus_sommer — Thu, 19 May 2016 07:11:58 GMT

Best would be you creates a small example with a few inline-data and a few screenshots or an excel to get an idea how the result should look like and which results you are expecting.

- Marcus