Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vishuraina
New Contributor II

Creating aggregated dimension from another dimension

Hi all,

Need urgent help on this one.

Lets say i have data in the below form :

itemsWeekSales
a1200
a2300
a3400
b1100
b2250
b4250
c1100
c2100


From this data i need to create a table which should be like this.

Field RequiredSum(Sales)
only a900
only b600
both a & b850
neither a nor b200


The 'Field Required' needs to be aggregated on weeks & items.

ex, In the required table 'only a' should have weeks which are present only in a i.e.,weeks 1,2,3 of a.

similar for 'only b'.

'both a & b' should have weeks which are common in a & b i.e., weeks 1 & 2 of both a & b.

likewise for 'neither a nor b', weeks without a & b should be present, i.e., c in this case .

Currently i'm using the below  expression for the Field Required:

=if(Aggr(items = 'a',WEEK,items),'only a',

if(Aggr(items = 'b',WEEK,items),'only b',

if(Aggr(items = 'a' and items = 'b',WEEK,items),'both a & b',

if(Aggr(items <> 'a' and items <> 'b',WEEK,items),'neither a nor b'))))

With this i'm able to get only a & only b, but unable to get the other 2.

Any help on this would be great.

1 Solution

Accepted Solutions

Re: Creating aggregated dimension from another dimension

May be try this

Pick(Match(ValueList('only a', 'only b', 'both a & b', 'neither a nor b'), 'only a', 'only b', 'both a & b', 'neither a nor b'),

Sum({<items = {'a'}>}Sales),

Sum({<items = {'b'}>}Sales),

Sum({<Week = p({<items = {'a'}>})*p({<items = {'b'}>}), items = {'a', 'b'}>}Sales),

Sum({<items -= {'a', 'b'}>}Sales))


/


RangeMin(

Sum({<items = {'a'}>}Sales),

Sum({<items = {'b'}>}Sales),

Sum({<Week = p({<items = {'a'}>})*p({<items = {'b'}>}), items = {'a', 'b'}>}Sales),

Sum({<items -= {'a', 'b'}>}Sales))


Capture.PNG

5 Replies

Re: Creating aggregated dimension from another dimension

This?

Capture.PNG

MVP
MVP

Re: Creating aggregated dimension from another dimension

You could also have each measure separately in a Pivot Table like this:

2018-04-18 16_38_02-Simple but Perplexing _ Data load editor - Qlik Sense.png

You simply add a dimension ='SALES' with the label Sales then you add the four measures as you can see in the first table above. After that you put the dimension on a column and the measures as rows in a Pivot Table.

vishuraina
New Contributor II

Re: Creating aggregated dimension from another dimension

Sunny,

Thanks for your help, this does work. Need to add one more column. which would take the minimum of the derived sales and divide it to the individual sales. ex: only a would become 4.5 (900/200) & likewise for others. Trying to figure out how do we get the minimum sales from here.

Re: Creating aggregated dimension from another dimension

May be try this

Pick(Match(ValueList('only a', 'only b', 'both a & b', 'neither a nor b'), 'only a', 'only b', 'both a & b', 'neither a nor b'),

Sum({<items = {'a'}>}Sales),

Sum({<items = {'b'}>}Sales),

Sum({<Week = p({<items = {'a'}>})*p({<items = {'b'}>}), items = {'a', 'b'}>}Sales),

Sum({<items -= {'a', 'b'}>}Sales))


/


RangeMin(

Sum({<items = {'a'}>}Sales),

Sum({<items = {'b'}>}Sales),

Sum({<Week = p({<items = {'a'}>})*p({<items = {'b'}>}), items = {'a', 'b'}>}Sales),

Sum({<items -= {'a', 'b'}>}Sales))


Capture.PNG

vishuraina
New Contributor II

Re: Creating aggregated dimension from another dimension

Thanks so much, this works perfectly.

Community Browser