Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master II
Master II

Need all columns from calculated dimension

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

20 Replies
Highlighted
Champion III
Champion III

Re: Need all columns from calculated dimension

did u tried in dimension tab

supppress wen value is null?

Highlighted
Master II
Master II

Re: Need all columns from calculated dimension

Tried and it is not working.

Highlighted

Re: Need all columns from calculated dimension

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 )

Highlighted
Champion III
Champion III

Re: Need all columns from calculated dimension

can u share a sample?

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

Highlighted
Master II
Master II

Re: Need all columns from calculated dimension

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


Highlighted

Re: Need all columns from calculated dimension

Hi,

May be like Attached?

Highlighted

Re: Need all columns from calculated dimension

May be like the attached:

Capture.PNG

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



Highlighted
Master II
Master II

Re: Need all columns from calculated dimension

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

Highlighted

Re: Need all columns from calculated dimension

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?