topic Re: populate all dates in a pivot table in QlikView

populate all dates in a pivot table

ali_hijazi — Wed, 17 Feb 2016 14:56:57 GMT

Hello

got 2 tables (transactions and master calendar)

both tables are linked by the date field

I have a pivot table with on dimension (date)

and 1 expression

what I want is the following

I select a range of dates say 1/1/2015 till 31/1/2015

now I have 2 transactions in 15/1/2015 and 17/1/2015

however I want the pivot table to display all the dates from 1/1/2015 till 31/1/2015 and so for the dates with no transactions I want to display zeros

Please advise

Re: populate all dates in a pivot table

Clever_Anjos — Wed, 17 Feb 2016 15:06:18 GMT

If your Dimension Table has all dates need just uncheck "Supress Zero Values" at Presentation tab

Re: populate all dates in a pivot table

eduardo_palacios — Wed, 17 Feb 2016 15:07:50 GMT

When you create the master calendar problably you are inputing only dates in transaction table.

Try this:

//Store the max data from transactions and min data from transactions

minMaxData:

Load

Num(Daystart(Min(DataField))) AS minData,

Num(Daystart(Max(DataField))) AS maxData

Resident

TransactionsTable

;

//Put the values into two variables

minData = Num(peek('minData'));

maxData = Num(peek('maxData'));

DROP TABLE minMaxData;

//Create Master Calendar with all values

AllData:

Load

$(minData) - 1 + rowno() AS DataField

AutoGenerate

($(maxData) - $(minData))

;

//CALENDAR IS CREATED BASE ON DISTINCT DATA TO INCREASE PERFORMANCE

//CALENDAR DATE IS REGULAR CALENDAR

//FISCAL DATES ARE BASED ON CALENDAR STARTING ON 1ST OF APRIL

// 1/APR/2011 (REGULAR CALENDAR) = 1/01/2012 ON FISCAL CALENDAR

Time:

LOAD

DataField,

MONTH(DataField) AS [Calendar Month],

ceil(MONTH(DataField)/6) as [Calendar Semester],

dual(YEAR(DataField) & '-H' & ceil(MONTH(DataField)/6), YEAR(DataField)*10+ceil (MONTH(DataField)/6)) as [Calendar SemesterYear] ,

ceil(MONTH(DataField)/3) as [Calendar Quarter],

dual(YEAR(DataField) & '-Q' & ceil (MONTH(DataField)/3), YEAR(DataField)*10+ceil (MONTH(DataField)/3)) as [Calendar QuarterYear] ,

num(MONTH(DataField)) AS [Calendar nMonth],

if(num(MONTH(DataField))>3,num( MONTH(DataField))-3, num( MONTH(DataField))+9) AS [Fiscal nMonth],

ceil(if(num( MONTH(DataField))>3,num( MONTH(DataField))-3, num( MONTH(DataField))+9)/3) as [Fiscal Quarter],

ceil( if( num( MONTH(DataField))>3,num( MONTH(DataField))-3, num( MONTH(DataField))+9)/6) as [Fiscal Semester],

dual(if(num( MONTH(DataField))>3,YEAR(DataField)+1, YEAR(DataField)) & '-H' & ceil( if( num( MONTH(DataField))>3,num( MONTH(DataField))-3, num( MONTH(DataField))+9)/6),

if( num( MONTH(DataField))>3,YEAR(DataField)+1, YEAR(DataField))*10+ceil( if( num( MONTH(DataField))>3,num( MONTH(DataField))-3, num( MONTH(DataField))+9)/6)) as [Fiscal SemesterYear] ,

dual( if( num( MONTH(DataField))>3,YEAR(DATA)+1, YEAR(DataField)) & '-Q' & ceil( if( num( MONTH(DataField))>3,num( MONTH(DataField))-3, num( MONTH(DataField))+9)/3),

if( num( MONTH(DataField))>3,YEAR(DataField)+1, YEAR(DataField))*10+ceil( if( num( MONTH(DataField))>3,num( MONTH(DataField))-3, num( MONTH(DataField))+9)/3)) as [Fiscal QuarterYear] ,

NUM(DataField) AS nDataField,

YEAR(DataField) AS [Calendar Year],

if( num( MONTH(DataField))>3,YEAR(DataField)+1, YEAR(DataField)) as [Fiscal Year],

MONTHSTART(DataField) as [Calendar Period],

if( num( MONTH(DataField))>3,

num(num( MONTH(DataField))-3,'00') & YEAR(DataField)+1,

num(num( MONTH(DataField))+9,'00') & YEAR(DataField)) AS [Fiscal Period]

RESIDENT

AllData

;

drop table AllData;

Also you need to enable the 'Show All Values' item on the Dimensions.

Hope it helps.

Re: populate all dates in a pivot table

rubenmarin — Wed, 17 Feb 2016 15:10:55 GMT

Hi Ali, in presentation tab, uncheck 'Supress zero values'. or upload a sample to check what other conditions are affecting.

For a simple sample it works just with that, PFA.

Re: populate all dates in a pivot table

ali_hijazi — Wed, 17 Feb 2016 15:16:15 GMT

I did that but it is not working

in the calendar I have all dates

but in Trx not all dates have transactions

Re: populate all dates in a pivot table

ali_hijazi — Wed, 17 Feb 2016 15:22:34 GMT

look in the transactions table I have a field called flag

and in my expression I have put a condition on the flag

sum({<flag={1}>}sales)

Re: populate all dates in a pivot table

rubenmarin — Wed, 17 Feb 2016 15:29:45 GMT

Can you try?:

Sum({<flag={1}>} sales)+Sum(0)

Re: populate all dates in a pivot table

Clever_Anjos — Wed, 17 Feb 2016 15:41:00 GMT

would you mind sharing a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

Re: populate all dates in a pivot table

marcus_sommer — Wed, 17 Feb 2016 15:54:01 GMT

There is no easy way to show not existing data and each of them have disadvantages.

If you uncheck surpress zero and enabled show all dimension values (by your date) and used then a (more or less complex) condition then you comes near to your wanted results, see:

If this isn't sufficient for you you would need to create a carthesian product to create all your missing data either within the script (amounts of data could be very big) or with calculated dimensions and complex expression (performance could be very slow).

- Marcus

Re: populate all dates in a pivot table

ali_hijazi — Wed, 17 Feb 2016 18:13:38 GMT

doing this will display all days of calendar

while I only want to display dates that are within the bracket of dates i'm selecting

SUM

(

{

[MOVEMENT FLAG]={'00'}

,FLAG={'RECEPTION'}

,S2TCNT = {'P'}

,Year=

,Month=

,Day=

// ,PeriodDate=

,PeriodDate={">= $(vFromDate) <=$(vToDate)"}

}S2QCNT

)

Re: populate all dates in a pivot table

Clever_Anjos — Wed, 17 Feb 2016 18:28:14 GMT

Try

SUM (

{

[MOVEMENT FLAG]={'00'}

,FLAG={'RECEPTION'}

,S2TCNT = {'P'}

,Year=

,Month=

,Day=

// ,PeriodDate=

,PeriodDate={">= $(vFromDate) <=$(vToDate)"}

}S2QCNT

)

+ 1/10000000000

Re: populate all dates in a pivot table

ali_hijazi — Thu, 18 Feb 2016 05:27:30 GMT

may you please send me a sample for the screen shot that you put?

Re: populate all dates in a pivot table

jagan — Thu, 18 Feb 2016 06:01:55 GMT

Hi,

Try like this

=sum({<flag={1}>}sales) + (0 * Sum(Sales)

Regards,

Jagan.

Re: populate all dates in a pivot table

rubenmarin — Thu, 18 Feb 2016 16:02:05 GMT

Hi Ali, the option to simple add a +Sum(0) didn't worked?

Re: populate all dates in a pivot table

marcus_sommer — Fri, 19 Feb 2016 08:09:48 GMT

Here an extend of the example-app from Ruben with a solution per conditions on the dimensions. But I must admit that I like more the solution from Ruben - you have now a choice and could check which solutions is in your real application more suitable.

- Marcus

Re: populate all dates in a pivot table

ali_hijazi — Fri, 19 Feb 2016 16:03:34 GMT

nop