topic Re: Zero values missing on X axis despite using Master Calendar in QlikView

Zero values missing on X axis despite using Master Calendar

Anonymous — Thu, 23 Mar 2017 09:03:14 GMT

I have used a master calendar to generate missing calendar months..

Missing months are appearing on the X axis as along as I uncheck 'Show All values' from the Dimensions tab

As a result even if I select specific few months to see the trend it shows all the months instead of the required months.

When i uncheck the 'Show all values' box the graph responds to the selection but the missing months dont appear.

Attached is my file.

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Thu, 23 Mar 2017 09:12:55 GMT

Try this expression which suppress zero values

Count(Application) + Avg({1<MonthOccurence = $::MonthOccurence>} 0.000000001)

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Thu, 23 Mar 2017 09:31:43 GMT

This is what i get.. I get numbers that are not readable. Also if i select Jan 2017 to May 2016 it shows rest of the months on the X axis till Feb 2016 instead of May 2016. I have suppressed zero values in Presentation tab

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Thu, 23 Mar 2017 09:36:46 GMT

I am getting this...

For number readability, change the number format to integer on the Numbers tab of the chart properties

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Thu, 23 Mar 2017 09:40:00 GMT

But July , Aug until Jan 2017 need to be present with the line touching zero

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Thu, 23 Mar 2017 09:43:47 GMT

May be try this

Count(Application) + Avg({1<MonthOccurence = {"$(='>=' & Min(MonthOccurence) & '<=' & Max(MonthOccurence))"}>} 0.000000001)

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Thu, 23 Mar 2017 10:02:58 GMT

When I put Impact Type as a Dimension it fails to touch zero for high line after June and shows a straight line from June to Aug instead of touching zero. For Medium impact i.e. red line as well after Sept ti should touch zero for Oct but jumps to Nov as 1.

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Thu, 23 Mar 2017 10:06:22 GMT

The combination of Month and Impact type is missing for July, Aug... Missing data problem here

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Thu, 23 Mar 2017 10:43:44 GMT

Thanks Sunny for your assistance. My reqt still stands unfilled though as the customer needs the missing data to be shown on X axis ..:(

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Thu, 23 Mar 2017 11:03:14 GMT

Then populate those missing areas just like you populated the missing dates using master calendar 🙂

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Thu, 23 Mar 2017 11:14:03 GMT

Thanks for the suggestion..Let me try it ..I am keeping this thread open and if I get a solution will close this thread.

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Thu, 23 Mar 2017 11:25:51 GMT

Sounds good....

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Tue, 04 Apr 2017 09:30:32 GMT

Hi Sunny

I made some headway based on what you said in terms of generating missing data for the missing calendar months.

I used the script from the blog of 'How to populate a sparsely populated field' by Henric Cronstrom

I am attaching the application. If you select the new application 'CONNECt' i get the missing months and the selection is as per the months selected and zero values appear for the missing months. But this works fine only for one application as in the script i have written

f(IsNull([Ticket Number]),'CONNECT',Application) as New_Application

Would you be able to suggest from your experience how do i link this to all the application i..e. i need to populate Application value for all the tickets wherever the ticket nuumber is blank. so effectively it should repeat with 0 zero value for all the applications.

any ideas on this are appreciated

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Tue, 04 Apr 2017 10:51:19 GMT

Would you be able to share this file?

Impact Trend.xlsx

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Wed, 05 Apr 2017 04:40:06 GMT

Attaching the sheet.. I did a workaround for this.

I created multiple rows with ticket number = 0 and attached application value to it by using

if(IsNull([Ticket Number]),'0',[Ticket Number]) as [Ticket Number],
if(IsNull([Ticket Number]),'CONNECT,HSBCNET,Clientsphere,Core Trade,CoreDocs,CRS,CWT,OKYC,RF,RM Portal,RRT,SCS AI',Application) as New_Application1,

Then in the expression i had to subtract the count of tickets iwht zero value.

fabs(count(distinct [Ticket Number])-count(distinct {<[Ticket Number]={'0'}>}[Ticket Number]))

Re: Zero values missing on X axis despite using Master Calendar

Anil_Babu_Samineni — Wed, 05 Apr 2017 05:01:34 GMT

Then in the expression i had to subtract the count of tickets iwht zero value.

fabs(count(distinct [Ticket Number])-count(distinct {<[Ticket Number]={'0'}>}[Ticket Number]))

May be this?

count({< [Ticket Number] = {'*'}-{'0'}>}[Ticket Number])

count({< [Ticket Number] -= {'0'}>}[Ticket Number])

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Wed, 05 Apr 2017 07:32:52 GMT

Is it working now?

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Wed, 05 Apr 2017 07:39:52 GMT

Check the attached now and see if this is the move towards the right direction

Re: Zero values missing on X axis despite using Master Calendar

Anonymous — Thu, 06 Apr 2017 04:48:37 GMT

Thanks Sunny...it works fine ..got the solution finally

Re: Zero values missing on X axis despite using Master Calendar

sunny_talwar — Thu, 06 Apr 2017 09:06:36 GMT

Nice, please close the thread by marking the correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny