Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Fellas ,
I am trying to keep up with set expression . But for this view i am just not good enough .
Please guide me to right expression to attain a view .
SOURE :
Company | Area | Profit | Loss | A1 | A2 | A3 | A4 | Glist |
Boeing | AREA1 | 37 | 27 | ABC | EFG | GGG | IJK | |
Boeing | AREA2 | 43 | 32 | |||||
Boeing | AREA3 | 51 | 25 | |||||
Boeing | AREA4 | 82 | 33 | |||||
Boeing | AREA5 | 68 | 26 | |||||
Boeing | 28 | |||||||
Mig | AREA1 | 77 | 34 | LMN | OPQ | RST | UVW | |
Mig | AREA2 | 33 | 29 | |||||
Mig | AREA3 | 94 | 24 | |||||
Mig | AREA4 | 96 | 23 | |||||
Mig | AREA5 | 30 | 33 | |||||
Mig | 33 | 40 | 75 |
Resulting
Company | Area | Profit | Loss | OUTPUT | Glist |
Boeing | AREA1 | 37 | 27 | ABC | 28 |
Boeing | AREA2 | 43 | 32 | EFG | 28 |
Boeing | AREA3 | 51 | 25 | GGG | 28 |
Boeing | AREA4 | 82 | 33 | IJK | 28 |
Boeing | AREA5 | 68 | 26 | 28 | |
Mig | AREA1 | 77 | 34 | LMN | 75 |
Mig | AREA2 | 33 | 29 | OPQ | 75 |
Mig | AREA3 | 94 | 24 | RST | 75 |
Mig | AREA4 | 96 | 23 | UVW | 75 |
Mig | AREA5 | 30 | 33 | 75 |
OUTPUT expression which i tried but in vain
If(Area='AREA1',Concat( {1<Area=,Company=p(Company)>}DISTINCT A1),If(Area='AREA2',
Concat( {1<Area=,Company=p(Company)>}DISTINCT A2),If(Area='AREA3',
Concat( {1<Area=,Company=p(Company)>}DISTINCT A3),If(Area='AREA4',
Concat( {1<Area=,Company=p(Company)>}DISTINCT A4))
)))
Glist expression which i tried
sum({<Area=>}Glist)
Help in correcting the expression .
You were pretty close. I think you were misunderstanding the meaning of the set {1}. {1} ignores selections, but it does not ignore the dimension. What you want is TOTAL. For Glist
sum(TOTAL<Company>Glist)
And for OUTPUT you could modify your If(), or use Pick(Match()) which is an alternative form.
pick(
Match(Area, 'AREA1', 'AREA2', 'AREA3', 'AREA4')
,concat(TOTAL <Company>A1)
,concat(TOTAL <Company>A2)
,concat(TOTAL <Company>A3)
,concat(TOTAL <Company>A4)
)
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
You were pretty close. I think you were misunderstanding the meaning of the set {1}. {1} ignores selections, but it does not ignore the dimension. What you want is TOTAL. For Glist
sum(TOTAL<Company>Glist)
And for OUTPUT you could modify your If(), or use Pick(Match()) which is an alternative form.
pick(
Match(Area, 'AREA1', 'AREA2', 'AREA3', 'AREA4')
,concat(TOTAL <Company>A1)
,concat(TOTAL <Company>A2)
,concat(TOTAL <Company>A3)
,concat(TOTAL <Company>A4)
)
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Thank You for the help . I think among contemporaries Qlik community is best and fast to suggestion .
If i have understood right , sum(TOTAL<Company>Glist) ---- means sum of glist after ignoring all dimension but Company .
Yes, your understanding is correct.
-Rob