topic Re: Pivot table in QlikView

Pivot table

Wed, 25 Nov 2020 16:16:04 GMT

i am facing a challenge in pivot tables as when i am trying to load the data from DB into pivot table with few filter conditions some of the rows are getting excluded so the corresponding dates are also getting excluded due to this there is date jump happening in date dimension but i want all the dates in date dimension, please help me in achieving this. below screenshot current status of the pivot table. thanks in advance.

Re: Pivot table

settu_periasamy — Wed, 09 Mar 2016 06:38:22 GMT

Hi,

Did you check you Day field? All day value are available?

And check, is there "Suppress When value is Null" checked in the Dimension Table?

Check your expression Also... Can you post the sample qvw?

Re: Pivot table

avinashelite — Wed, 09 Mar 2016 06:41:17 GMT

Hi Pradeep,

1.check whether your field has all the data in it..add a list box and check whether your able to see all the data or not...

2.if your able to see the data then check your expression condition due to this also certain values might get eliminated

3.Check the show all values in the dimension tab

Re: Pivot table

Wed, 09 Mar 2016 07:33:46 GMT

thanks for your response.

i have all the day fields in DB but due to filter conditions few dates are getting excluded while loading into pivot and this is causing a jump in dates as above, but i want all the dates in pivot table and has to populate value '0' under corresponding dates which are excluded. please help me out on this.

1. Suppress when value is not Checked.

and i am sorry i cant share sample QVW file. since it is confidential.

Re: Pivot table

Wed, 09 Mar 2016 07:58:59 GMT

Thanks for the response.

1. I can see all the dates in the list box. but these dates has got 0 value in the table so i guess when doing group by with Sum aggregation this is getting ignored.

2. there is no expression conditions set.

please help me on this.

Re: Pivot table

Digvijay_Singh — Wed, 09 Mar 2016 08:27:01 GMT

Check 'Show All values' in dimension tab for date dimension.

Re: Pivot table

Wed, 09 Mar 2016 08:56:42 GMT

hi,

show all values are checked.

here when i am trying to retrieve only necessary data (i.e, filter conditions) few rows are not getting picked so ultimately dates are missing in graph causing jump in date trend.

please help me.

Re: Pivot table

avinashelite — Wed, 09 Mar 2016 11:56:23 GMT

Hi Pradeep,

do have the data in Qlikview ?? for all the missing dates ? add the list box and add your date field and check whether you have all the dates or not. If you don't have the data then create a master calendar and link you date column with the same.

Generating Missing Data In QlikView

Re: Pivot table

avinashelite — Wed, 09 Mar 2016 11:57:54 GMT

try like this :

Go to chart properties > presentation tab >uncheck the suppress zero value

Re: Pivot table

Digvijay_Singh — Wed, 09 Mar 2016 12:16:05 GMT

It means you want to ignore selection, use <Date=,> modifier in the set section of your Sum expression. This way it will show all the dates.

Re: Pivot table

settu_periasamy — Wed, 09 Mar 2016 13:30:31 GMT

what do you mean by filtering condition? Can you share your expression?

Re: Pivot table

Wed, 09 Mar 2016 16:31:19 GMT

hi Avinash,

It actually worked thanks for your valuable time in this course.

and also I have unchecked suppress Null values in Dimension tab and I got the output that I desired.

Re: Pivot table

avinashelite — Wed, 09 Mar 2016 17:32:52 GMT

cool good to hear that i was able to help you

if you have got the answer please mark the correct and helpful answers , so that it will be helpful for others who are having the similar question