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
Are you talking about the green part of the expression? I had to change your expression from what you had to this because the sample data you shared did not have the fields you use within your count function. But to get my point across I had to change that to something else and VirNormQty was the only field that would have worked here...
Is that what confused you or is there something else you don't understand?
Hi Pradosh,
Thanks for giving time over this.
I want to know why are using pick(match(10,....) havent got this 10?
Please explain!
Yeah I am talking about green part. I am confused over Last three lines of your reply
Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))*
Count(Distinct VisNormQty)
,VisibilityNorm, [Vis_Product Class]), ', ')
So If I am getting you correct you are aggregation over three parameters
1. Count(Distinct VisNormQty), // replaced with my field later
2. VisibilityNorm
3. Vis_product Class
but when I am using your stated expression it is giving me error. Because of *Count(Distinct VisNormQty) this.
Secondly
why have you used ',' at the end
I am only aggregating over two dimensions.... VisibilityNorm and Vis_product Class.... In fact there is nothing like aggregating over an expression...
This is what you shared, right?
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)
Since, the sample data you shared did not include AccountChannel, Max_Vis_Flag and dealer_code_c... I changed the expression to this
Count(Distinct VisNormQty)
All you need is 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({<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c)
,VisibilityNorm, [Vis_Product Class]))
The comma at the end was by mistake... I copy and pasted the Concat expression and replaced Concat with Sum.... Concat used comma as a delimiter, but sum doesn't need it. It was my bad
Thanks for so much of patience.
Got you now!
I have used this approach as well but output is not coming correct. It simply multiply normqty with dealer count without taking care of VisibilityNorm
just clear one point, is it possible/feasible to plot my above scenario on a single dimension i.e vis_product class?
now I am completely confused over this.
I think it is def. possible but I am just not sure what you want your final output to look like when you have multiple fields.... I am going to give you an example, and you tell me what the final output needed is...
When you have two dimensions
A B Measure
ABC DEF 10
ABC KLM 20
Not let's say you want to keep only A here....
A Measure
ABC What should come here? 30 (Sum)? 15 (Avg)? 10 (Min)? 20 (Max)? or something else?
10 is just a dummy number. It can be replaced by anything other than 1 or 2 that is returned by your expression
alt returns 10 if yourexpression is null
so simplifying it
match(10,alt(yourexpression,10)) will return 0 when your expression is not null as it will return 1 or 2 . I have added + 1 so it will pick the first expression inside picki that is your_expression * count_expression.
secondly when there is a null returned, which will be the case when no filter is selected in VisibilityNorm. alt will return 10. and match will match it and return 1. +1 will return 2. so pick will pick the second expression. . you can see what i tried there was to incliude all the visibilty norm ion the set expression ..
Sunny rightly asked what you want to do when there is no filter selected sum? min? avg? max ?
you have to replace in my second expression or else you can try stalwar1 solution as well which is less lengthy and looks better.