Show continuous months irrespective of data - Qlik Community

Anonymous · ‎2011-05-31

Hi Friends,

In a transaction table I maintain data as per fiscal year(Apr-Mar).

In the transaction table I have only 6 months data in scattered manner.

I have data for the months Apr,MayJune,July,Nov,Dec only

I want to make a report to show all the months with their data if available in transaction table.

If data are missing for a month it will show 0 value.

Transaction table:

Field Date Plant

001 30/04/2010 100

002 31/05/2010 200

001 30/06/2010 400

002 31/07/2010 300

001 31/12/2010 900

002 30/11/2010 500

Output:

Field Month Plant

001 Apr 100

001 May 0

001 Jun 400

001 Jul 0

001 Aug 0

001 Sep 0

001 Oct 0

001 Nov 0

001 Dec 900

001 Jan 0

001 Feb 0

001 Mar 0

002 Apr 0

002 May 200

002 Jun 0

002 Jul 300

002 Aug 0

002 Sep 0

002 Oct 0

002 Nov 500

002 Dec 0

002 Jan 0

002 Feb 0

002 Mar 0

Pls help me

regards,

chandan

prieper · ‎2011-05-31

In a graphic you may choose continuous values on the axis, but this does not help, if the start- or end-date has no data ...

You may create a cartesian product for each date (Date, Week, or Month) betw start- and end-date and Field. During the loading-process you then join values for plant into it.

HTH
Peter