Need all columns from calculated dimension

[trdandamudi]

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

[Chanty4u]

did u tried in dimension tab

supppress wen value is null?

[trdandamudi]

Tried and it is not working.

[avinashelite]

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 )

[Chanty4u]

can u share a sample?

or else chk with ur metrics in textbox once?  and tablebox aswell

[trdandamudi]

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

[settu_periasamy]

Hi,

May be like Attached?

[sunny_talwar]

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')

[trdandamudi]

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

[sunny_talwar]

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?