topic Re: Need all columns from calculated dimension in QlikView

Need all columns from calculated dimension

trdandamudi — Thu, 17 Mar 2016 12:10:51 GMT

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

Re: Need all columns from calculated dimension

Chanty4u — Thu, 17 Mar 2016 12:12:36 GMT

did u tried in dimension tab

supppress wen value is null?

Re: Need all columns from calculated dimension

trdandamudi — Thu, 17 Mar 2016 12:17:40 GMT

Tried and it is not working.

Re: Need all columns from calculated dimension

avinashelite — Thu, 17 Mar 2016 12:18:31 GMT

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 )

Re: Need all columns from calculated dimension

Chanty4u — Thu, 17 Mar 2016 12:18:41 GMT

can u share a sample?

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

Re: Need all columns from calculated dimension

trdandamudi — Thu, 17 Mar 2016 12:22:24 GMT

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

Re: Need all columns from calculated dimension

settu_periasamy — Thu, 17 Mar 2016 12:24:26 GMT

Hi,

May be like Attached?

Re: Need all columns from calculated dimension

sunny_talwar — Thu, 17 Mar 2016 13:02:20 GMT

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

Re: Need all columns from calculated dimension

trdandamudi — Thu, 17 Mar 2016 20:40:11 GMT

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

Re: Need all columns from calculated dimension

sunny_talwar — Thu, 17 Mar 2016 20:44:39 GMT

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?

Re: Need all columns from calculated dimension

trdandamudi — Thu, 17 Mar 2016 20:53:18 GMT

Got It and I think i missed that one and now it shows only three columns.

What is happening is it is showing all the records even though I selected one Num_Id. Please see the attached screen shot.

Thanks

Re: Need all columns from calculated dimension

sunny_talwar — Thu, 17 Mar 2016 20:56:26 GMT

So you want to see High Medium Low, but for the selected Num_ID?

Re: Need all columns from calculated dimension

trdandamudi — Thu, 17 Mar 2016 21:00:37 GMT

Yes, Sometime we want to see by Num_ID, by Place, by Place_code etc. Will this be an issue ?

Re: Need all columns from calculated dimension

sunny_talwar — Fri, 18 Mar 2016 00:27:27 GMT

What would you see if you make a selection in Num_ID, Place, Place_code? Rows get removed, but High, Low, Medium stay intact? I am going to give it a try, but no promises

Re: Need all columns from calculated dimension

trdandamudi — Fri, 18 Mar 2016 00:38:30 GMT

Thanks for the follow up...

Yes, All the headers has to stay including Low, Medium and High. Attached is a .jpg file showing how it is going to look when you selected Num_Id. I edited the screen shot in the paint program and hope this gives an idea,

Thanks.

Re: Need all columns from calculated dimension

sunny_talwar — Fri, 18 Mar 2016 00:59:27 GMT

I am not seeing Low and Medium in the screenshot when 100001 is selected, is that what you want? I am getting a little confused now. Do you want to see High, Medium, Low when you select 100001 is selected or just High? and in your screenshot you have Detail_descr also selected. How does that affect your display?

Re: Need all columns from calculated dimension

trdandamudi — Fri, 18 Mar 2016 01:09:24 GMT

Sorry for the confusion...I was not able to display the Low and Medium..

Yes, I want to see Low, Medium and High columns when I select 100001.

You no need to worry about the 'Detail_descr' filter because when you select 100001 it will select it automatically. As i said later on I will add more filter boxes like Place_Code, Place_Details,Place etc.. and I should be able to select any one of the filters.

Hope this is clear..

Re: Need all columns from calculated dimension

trdandamudi — Fri, 18 Mar 2016 01:15:46 GMT

Here is the updated .jpg file. Hope this makes sense now.

Re: Need all columns from calculated dimension

sunny_talwar — Fri, 18 Mar 2016 01:18:23 GMT

It makes sense, but unfortunately, I don't think this would be possible. I am trying to see if it would be, but I doubt.

Re: Need all columns from calculated dimension

sunny_talwar — Fri, 18 Mar 2016 14:30:06 GMT

Not able to do it , sorry