topic Summarizing based on Date in QlikView

Summarizing based on Date

Wed, 02 Feb 2011 14:06:49 GMT

I have a date field in my database (MM/DD/YYYY) and would like to be able to summarize by year and or month. How can I display just month in the 'X" axis of a chart?

Thanx

Summarizing based on Date

Wed, 02 Feb 2011 14:10:29 GMT

use this :

Day(Date_Field) for day

Month(date_Field) for Month

Year(Date_field) for Year

Summarizing based on Date

Wed, 02 Feb 2011 14:16:28 GMT

HI,

You have to do a table calendar

See below exemple.




Calendar:
Load
Date,
Year(Date) AS Year,
'Q' & ceil(month(Date)/3) AS Quarter,
Month(Date) AS Month,
MonthName(Date) AS MonthName,
Week(Date) AS Week,
WeekName(Date) AS WeekName,
Day(Date) AS Day,
WeekDay(Date) AS WeekDay;
Load Distinct Date
From FactTable;

Summarizing based on Date

Wed, 02 Feb 2011 14:18:29 GMT

Hi Ashwin

Thanx for the response.....forgive my igonrance, but where do I put that syntax? Is it in the expression which I dont understand because it is a selected field or is it in the script.

Thanx and sorry

Summarizing based on Date

Wed, 02 Feb 2011 14:27:23 GMT

Hi Fernando,

I do not have a Calendar table. That being said, where would your suggested script go? Within the Load portion of my table or does it get it's own load...I am new to Qlikview and have not yet been able to attend training so if the question seems elementary I apologize.

Thanx

Summarizing based on Date

suniljain — Wed, 02 Feb 2011 14:32:52 GMT

TABLE:

load

SUM(X) AS amt,

MONTH(date) AS month,YEAR(date) AS year

resident abc group BY MONTH(date) ,YEAR(date) ;

Summarizing based on Date

Wed, 02 Feb 2011 14:58:28 GMT

HI,

The best practices of the QlikView recommends using a calendar table.

See the files in My Files.

http://community.qlik.com/members/ftonial/files/default.aspx

Summarizing based on Date

Wed, 02 Feb 2011 15:11:22 GMT

Hi,

Those load statements worked.......what would be the syntax for Month Year? (MM/YYYY

Summarizing based on Date

Wed, 02 Feb 2011 16:13:23 GMT

Hi,

Date(Date,'MM/YYYY') as MonthYear

Summarizing based on Date

suniljain — Thu, 03 Feb 2011 13:09:04 GMT

Dear Fernando,

Thanx , I alredy ahve that file and enough material that I already referred.

Regards

Sunil

Summarizing based on Date

Thu, 03 Feb 2011 21:34:12 GMT

This works but it doesn't group the data in the list box. I am still trying to figure out how to get the data to roll up by Month, Year and Month/Year

Summarizing based on Date

Thu, 03 Feb 2011 22:19:14 GMT

put your code here to check the problem

Summarizing based on Date

Thu, 03 Feb 2011 22:24:17 GMT

Load Order__c,
Month(Billing_Date__c) as Month,
Year(Billing_Date__c) as Year
Resident Order__c
Group by Month, Year;

Summarizing based on Date

Thu, 03 Feb 2011 22:36:02 GMT

The code is perfect for me, try this.


Load Order__c,
Month(Billing_Date__c) as Month,
Date(Billing_Date__c,'MM/YYYY') as MonthYear1,
MonthName(Billing_Date__c) as MonthYear2,
Year(Billing_Date__c) as Year
Resident Order__c
Group by Month, Year;