Counting in Qlikview

[#1]

I am querying two issues:

1) Is it possible to have a list of dates formatted as :

               01/01/2012

               02/01/2012

               03/01/2012

               04/01/2012

               05/01/2012

               06/01/2012

All the way up to the current date today 23/11/2012, to seperate the dates by Month? so maybe have it formatted to look like

     January +

     February +

     March +

     April +

This would then expand to list the dates within January etc.

2) With this list date I would be able to write an if statement which looks at the the day to calculate the total number of instances on that given day? and then do the same for the month field?

[Anonymous]

Hi Sandston,

This should give you a calendar table with Dates, Months, Years and Quarters from Jan 1st 2012 until Today (Set by variables):

Let vStartDate=num(date#('2012-01-01', 'YYYY-MM-DD'));

Let vEndDate=Today();

Load

          Date(DateNum) as Date,

          Year(DateNum) as Year,

          Month(DateNum) as Month,

          Ceil(Month(DateNum)/3) as Quarter,

          Year(DateNum) & Week(DateNum) as YearWeek,

          DateNum;

Load

          $(vStartDate)+RecNo() as DateNum

AutoGenerate $(vEndDate)-$(vStartDate);

Then for expanding Months into Dates etc., look at the Pivot table chart object

[renjithpl]

For month you can try

date(Date, 'MMMMM') & ' +' as Month

[#4]

There is also the tree view functionality in the listbox.  See attached example.

For counting the dates, take a look at the Count() function.  Depening on what you want to achieve, you may or may not want to use the distinct keyword.  I have put an example of this in the attached example too.

[#5]

Hi I am unable to load your example as I am only using the personal free edition.

I was away for last four days so will be picking this back up now.

[#6]

Hi i added the lines into my edit script but cant see when finally loads. have the error message also when I click the reload button.

[#7]

I have attached a sample of the data i am using. hope this helps

[#8]

Any further assistance greatly appreciated.