Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I did posted a question on this few days back and I got few responses.But none of them are working the way I want it. So I thought let me post it once gain to see if some one can resolve this.
I have a pivot chart with calculated dimension. The calculated dimension creates three columns like Low, Medium and High. If I have the data for all the three columns then everything is fine, But if I have values for only one column then it doesn't display the other two columns with zeros.
Tried below:
"Show All Values" option is greyed out because it is Calculated dimension, so this will not work.
un checking "Suppress Zero Values" will not work.
I think there should be some easy way to do this but I am not able to find it. Can some one please let me know if this can be done in Qlikview?
I am attaching the sample Qlikview dashboard and the output that is required.
Thanks
did u tried in dimension tab
supppress wen value is null?
Tried and it is not working.
Have you un checked the Suppress null value in the presentation tab ??
can you please share the expression and the calculated dimension ? (don't have QV license )
can u share a sample?
or else chk with ur metrics in textbox once? and tablebox aswell
Yes, checked it but not working..
Dimension:
=Aggr(If(avg(Aggr(Sum(MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) <=60,'Low',
If(avg(Aggr(Sum(MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 60 And avg(Aggr(Sum(MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) <=100,'Medium',
If(avg(Aggr(Sum(MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 100,'High'))),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)
Expression 1: Items
=Count(Place)
Expression 2: Used
=avg(Aggr(count(TypeOfDay),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num))
Expression 3: New
=avg(Aggr(Sum(MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num))
Thanks
Hi,
May be like Attached?
May be like the attached:
Calculated Dimension
=Aggr(If(avg({<Detail_Descr>}Aggr(Sum({<Detail_Descr>}MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID)) <=60,'Low',
If(avg({<Detail_Descr>}Aggr(Sum({<Detail_Descr>}MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID)) > 60 And
avg({<Detail_Descr>}Aggr(Sum({<Detail_Descr>}MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID)) <=100,'Medium',
If(avg({<Detail_Descr>}Aggr(Sum({<Detail_Descr>}MFrontTrips)/2,Place_Code,Place_Detail,Place,Num_ID)) > 100,'High'))),
Place_Code,Place_Detail,Place,Num_ID)
Expression1:
=If(Count({1}Place) > 0 and IsNull(Count(Place)), 0, Count(Place))
Expression2:
=If(Avg({1}Aggr(Count({1}TypeOfDay),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 0 and
IsNull(Avg(Aggr(Count(TypeOfDay),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num))), 0, Avg(Aggr(Count(TypeOfDay),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)))
Expression3:
=If(Avg({1}Aggr(Sum({1}MFrontTrips),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 0 and
IsNull(Avg(Aggr(Sum(MFrontTrips),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num))), 0, Avg(Aggr(Sum(MFrontTrips),Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)))
Sorting expression for calculated dimension:
=Match(Only({1}Aggr(If(avg({1}Aggr(Sum({1}MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) <=60,'Low',
If(avg({1}Aggr(Sum({1}MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 60 And
avg({1}Aggr(Sum({1}MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) <=100,'Medium',
If(avg({1}Aggr(Sum({1}MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 100,'High'))),Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)),
'Low', 'Medium', 'High')
Sunny,
I am trying to incorporate your changes into my dashboard and I am having a tough time with the dimension. The dimension is creating the three columns Low, Medium. High. But it is also creating a fourth column with blank ?
By the way the filter box will be by 'Num_Id' and not by 'Detail_Descr'. it was a mistake from my side.
Do I need to do any check or uncheck to get rid of that blank column ?
Thanks
Def. have 'Suppress When Value Is Null' on the dimension tab for the calculated dimension so that the fourth column doesn't show up. What other problems are you seeing?