topic Re: Set Analysis in QlikView

Set Analysis

psankepalli — Wed, 18 May 2016 15:48:44 GMT

Hello Experts,

I have the following code,

Fact:

LOAD *

Inline

[

Key, Value

1,10

1,20

2,5

];

DIMENSION:

LOAD *

Inline

[

Key, Brand

1,ABC

2,ABC1

];

Exit Script;

My requirement is whenever I select ABC from Dimension, I need a value of both ABC + ABC1 value and

BRAND	SUM(VALUE)
ABC	35
ABC1	0

How to achieve this unique requirement? I don't mind to use any type of set Analysis.

I want to acheive this unique requirement.

Please help.

Thanks

Re: Set Analysis

Gysbert_Wassenaar — Wed, 18 May 2016 15:58:32 GMT

Use this script and use BRAND as chart dimension instead of Brand.

Fact:

LOAD *

Inline

[

Key, Value

1,10

1,20

2,5

];

DIMENSION:

LOAD *

Inline

[

Key, Brand, BRAND

1,ABC, ABC

2,ABC1, ABC

0,,ABC1

];

Re: Set Analysis

Gabriel — Wed, 18 May 2016 16:16:41 GMT

Hi,

Try this Set Analysis

=Sum( {$<Brand={"*ABC*"}>}Value)

Re: Set Analysis

psankepalli — Wed, 18 May 2016 17:21:08 GMT

Hi Gysbert,

I cannot change the values in the script, this INLINE data is just sample of my Band data.

I have brands like this

ABC,

ABC1

ABC2

XYZ

XYZ1

XYZ2.. etc

I need show values at ABC & XYZ group level.

Brand	Sum(Brand)
ABC	30
ABC1	0
ABC2	0
XYZ	40
XYZ1	0
XYZ2	0

Not sure how to address it.

Please help.

Re: Set Analysis

psankepalli — Wed, 18 May 2016 17:21:56 GMT

Hi Gabriel,

Thank you for your reply,

I tried this one, but this is not fulfilling my requirement. Any other suggestion.

Thanks

Re: Set Analysis

Wed, 18 May 2016 19:09:24 GMT

Ultimately this is a data cleansing issue more than a set analysis issue and if you will have many charts that use this logic you should look at cleaning the data in the load script if at all possible. There is obviously two data values combined in the Brand field.

Having said that, this expression will do what you want. It adds up every value associated with the selected brand.

sum({1<Brand = {'$(=getFieldSelections(Brand))*'}>} Value)

Re: Set Analysis

maxgro — Wed, 18 May 2016 19:47:02 GMT

maybe with a calculated dim

=PurgeChar(Brand, '0123456789')

Re: Set Analysis

psankepalli — Thu, 19 May 2016 02:12:15 GMT

Hi Max,

Thank you for your reply, unfortunately my data is not linke that, that's only for example. My data is as below

ABC

ABC-ASL

ABC-NBC

XYZ

XYZ-ASL

XYZ-NBC

XYZ-NML

IN THIS, I want to show all brands in straight table but sum of the ABC group values at ABC, and remaining ABC groups should be zero. Same for XYZ too.

Not sure how to solve this

Please advise

Re: Set Analysis

settu_periasamy — Thu, 19 May 2016 04:05:47 GMT

May be something like attached

I tried with valuelist..

Re: Set Analysis

PrashantSangle — Thu, 19 May 2016 05:10:05 GMT

Hi,

another solution can be

Test:

LOAD * INLINE [

Dim, Sales

ABC, 10

ABC-ASL, 20

ABC-NBC, 30

XYZ, 20

XYZ-ASL, 30

XYZ-NBC, 40

XYZ-NML, 50

];

JOIN

LOAD SubField(Dim,'-',1) as Dim,

sum(Sales) as TotalSales

Resident Test

Group by SubField(Dim,'-',1);

Then in front end

take straight table

Dimension -> Dim

Expression ->

1: Sum(Sales)

2: Sum(Total Sales)

If you don't required sum(Sales) then hide it in Presentation tab but don't remove it.

Regards

Re: Set Analysis

Kushal_Chawda — Thu, 19 May 2016 05:33:37 GMT

Try this

Create Straight table

Dimension:

Brand

Expression:

alt(Pick (match(Brand,'ABC','XYZ'),

sum({<Brand={"ABC*"}>}Sales),

sum({<Brand={"XYZ*"}>}Sales)),0)

alt(Pick (match(Brand,'ABC','XYZ'),

sum({<Brand={"ABC*"}>} total <Brand >Sales),

sum({<Brand={"XYZ*"}>} total <Brand >Sales)),0)

Re: Set Analysis

psankepalli — Thu, 19 May 2016 13:41:48 GMT

Thank you all for your help.

Re: Set Analysis

psankepalli — Thu, 19 May 2016 13:42:38 GMT

Hi Settu,

Thank you very much, this is what I am looking for. if any change in requirement I will get back to you.

Once again thank you very much for your help.

Thanks