Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Fellas,
Need your help
VisibilityNorm:
LOAD * INLINE [
VisibilityNorm, Vis_Product Class, VisNormQty
DGATop8,PREMIUM,2
DGATop8,SUPER PREMIUM,2
DGATop8,MEDIUM,1
DGBNext45,SUPER PREMIUM,2
DGBNext45,PREMIUM,2
DGBNext45,MEDIUM,2
DGRest,PREMIUM,1
DGRest,MEDIUM,1
DGRest,MASS,2
NA,NA,0
SATop8,PREMIUM,1
SATop8,SUPER PREMIUM,1
SATop8,MEDIUM,2
SBNext45,PREMIUM,1
SBNext45,SUPER PREMIUM,1
SBNext45,MEDIUM,1
SBNext45,MASS,1
SRest,PREMIUM,1
SRest,SUPER PREMIUM,1
SRest,MEDIUM,1
SRest,MASS,1
BATop8,PREMIUM,1
BATop8,MEDIUM,1
BBNext45,MEDIUM,1
BBNext45,MASS,1
BRest,MEDIUM,1
BRest,MASS,1
];
I am using this expression
Norm Qty=
If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),
If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),
If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),
If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),
If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))*
Count({<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c)
My result looks like this
Vis_Product Class Norm_Qty
SUPER PREMIUM -
PREMIUM -
MEDIUM -
MASS -
Now If I select DGATop8 on Visibility Norm filter
Then It shows me the desired result
Vis_Product Class Norm_Qty
SUPER PREMIUM 2
PREMIUM 2
MEDIUM 2
MASS -
I need to execute above expression in a way that it should work without filter. I have also tried to Aggr but end up with the same result.
Thanks in advance
try this may be
alt(If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),
If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),
If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),
If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),
If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA'))))))))),1)*
Count({<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c)
May be you need to add VisibilityNorm as one of your dimension here? Because there are multiple VisibilityNorm, the chart doesn't know whoes value it needs to show. Or you may be want to show Min/Max/Avg/Sum from them?
Thanks a ton Pradosh, but this helped me partially!!
Yeah Sunny I am seeking for a way to take out data corresponding Vis_Product Class in my final output though I am getting your point of plotting other dimension. And actually when i plot other field in my output table it result me correct. but need to script or write expression in a way that I will be able to take information corrresponding to mentioned field.
Or I expect that this information can be plot over two-dimensions only.
so when nothing is selected you want to see the total
try this or sunny may help you more.
ps: code edited since the original post
pick(match(10,
alt(If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),
If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),
If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),
If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),
If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA'))))))))),10))+1,
Count({<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c)*
If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),
If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),
If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),
If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),
If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))
,
Count(total <VisibilityNorm> {<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c))
Can you please explain your expression once!
The above expression will return the corresponding value when VisibilityNorm is selected if not then it will show the total for Vis_Product Class for all the VisibilityNorm. at least that is what i was trying .
if the above is not showing the desired result than try replacing
Count(total <VisibilityNorm> {<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c))
with
Count( {<AccountChannel={'GT'}, Max_Vis_Flag={'1'},VisibilityNorm=>}Distinct dealer_code_c))
You see how there are multiple values when we use your expression (modified version of your expression)
Which one should Qlik be showing as the value? Sum them? Avg them? Find Min or Max? What exactly do you want to see as the output?
For Sum, you can do this
Sum(Aggr(
If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),
If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),
If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),
If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),
If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),
If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),
If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))*
Count(Distinct VisNormQty)
,VisibilityNorm, [Vis_Product Class]), ', ')
Change Sum to whatever type of aggregation you wish to see....
Hey Sunny,
i got your point, could you please help me in understanding below mentioned part of the expression. Though i have alreay use aggregation sum over visibility norm and vis_product class.
Count(Distinct VisNormQty),
,VisibilityNorm, [Vis_Product Class]), ', ')
But as expected it multiplies dealers with norm without considering the norm.
As much i have understood, I need to plot both the dimensions in the table to get the correct data since we are putting condition using two different dimensions then how can i plot data corresponding to single dimension.
I hope i am making some sense, else suggest me some other way to get out of this problem.