Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

frankycardinale
New Contributor

Measure divided between (team of) two dimensions / columns

Hello

I have an input table with two dimensions <am1 and am2> and one measure <sales>, like the following:

am1am2sales
pluto7
plutopippo4
plutotopolino10
pippo1
pippopluto6
pippotopolino2
topolino5
topolinopluto4
Total39

I would like to get an output table, in the app, like the following:

  

amam teamsalessales by am
plutopluto77
plutopluto - pippo42
plutopluto - topolino105
plutopippo - pluto63
plutotopolino - pluto42
pippopippo11
pippopippo - pluto63
pippopippo - topolino21
pippopluto - pippo42
topolinotopolino55
topolinotopolino - pluto42
topolinopluto - topolino105
topolinopippo - topolino21
Totale39

In wich I have:

- am: all am (am1 or am2)

- am team: am1 + am2

- sales: sales of 'am team'

- sales by am: if 'am team' is composed of 1 am -> sales, if 'am team' is composed of 2 am -> sales/2

how can I get this result?


Thanks

EDIT 30/08/2018.

The input table is actually just an extraction I posted as an example. It belongs to a complex snow-flake scheme defined in Qlik.
am1 and am2 are fields of two dimension tables, sales is the field of a fact table. The fact table is connected to the two dimensions via id fields.

Tags (1)
4 Replies
MVP
MVP

Re: Measure divided between (team of) two dimensions / columns

Here is a load script that will transform your input into the output you need:

T1:

LOAD

  If( IterNo()=1, am1 , am2 ) AS am,

  am1 & If( ams=2 , '-' & am2) AS [am team],

  sales,

  If( ams=2 , sales/2 , sales ) AS [sales by am]

WHILE

  IterNo()<=ams;

LOAD

  am1,

  am2,

  sales,

  If( am2 = '' , 1 , 2 ) AS ams

INLINE [

am1,am2,sales

pluto,,7

pluto,pippo,4

pluto,topolino,10

pippo,,1

pippo,pluto,6

pippo,topolino,2

topolino,,5

topolino,pluto,4

];

2018-08-08 01_21_26-Settings.png

acardella
New Contributor III

Re: Measure divided between (team of) two dimensions / columns

Hi Francesco,

you can try with this...

bye

Script

LOAD

*,

SubField([am team],' - ') as am1

;

LOAD

am1& if(Len(am2)>0, ' - ' & am2) as [am team],

sales,

if(Len(am2)>0,sales/2,sales) as [sales by am]

;

LOAD * INLINE [

    am1, am2, sales

    pluto, , 7

    pluto, pippo, 4

    pluto, topolino, 10

    pippo, , 1

    pippo, pluto, 6

    pippo, topolino, 2

    topolino, , 5

    topolino, pluto, 4

];

frankycardinale
New Contributor

Re: Measure divided between (team of) two dimensions / columns

Sorry, I have edited the answer.

I cannot use your script because my input table is a complex snow-flake scheme.

Thanks a lot for your solution.

frankycardinale
New Contributor

Re: Measure divided between (team of) two dimensions / columns

Sorry, I have edited the answer.

I cannot use your script because my input table is a complex snow-flake scheme.

Thanks a lot for your solution.