topic Re: group by date SQL Qlik Sense in App Development

group by date SQL Qlik Sense

joeybird — Fri, 02 Oct 2015 10:36:35 GMT

Hiya

I have a table loaded in via a SQL server,

however, the filter pain for Order_Date

is showing multiple order date instances

e.g

04/04/2015

06/04/2015

07/04/2015

I wish it to show

04/04/2015

06/04/2015

07/04/2015

is there a way of grouping by the date??

I have a calendar, and the based on the order date

I have tried

day(Order Date) in expression

and the same happens

please help

Re: group by date SQL Qlik Sense

ogster1974 — Fri, 02 Oct 2015 10:43:01 GMT

Create a Master Calendar to link to your transactional data on order date.

Lots of resources on the site around how to do this.

Regards

Andy

Re: group by date SQL Qlik Sense

ogster1974 — Fri, 02 Oct 2015 10:44:04 GMT

This should get you started on the prinicples

Understanding the Master Calendar (video)

Re: group by date SQL Qlik Sense

joeybird — Fri, 02 Oct 2015 10:46:59 GMT

Hiya

I have like this and have the same issue

Calendar: DECLARE FIELD DEFINITION TAGGED '$date'
Parameters first_month_of_year = 1,
fiscal_first_month_of_year = 4

Fields

     Year($1) As Year Tagged '$year',

     if(Year($1) >= 2010, Year($1)) as DropDownYear,

     if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear',


     Month($1) as Month Tagged '$month',

     if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',


     Date($1) as Date Tagged ('$date', '$day'),

Week($1) as Week Tagged '$week',

Weekday($1) as Weekday Tagged '$weekday',

'Q' & Ceil(Month($1)/3) as FinancialQuarter Tagged '$FinancialQuarter';

DERIVE FIELDS FROM FIELDS [Order_Date] USING Calendar;

and still no joy

please help

Re: group by date SQL Qlik Sense

ogster1974 — Fri, 02 Oct 2015 10:55:27 GMT

If you make your date field in your calendar Order Date Qlik will join your Order table with this Calendar on Order Date and your group issue should be resolved. Rename this OrderCalendar and the other fields incase you need more than one in your model.

Re: group by date SQL Qlik Sense

joeybird — Fri, 02 Oct 2015 11:16:02 GMT

Hiya

both fields are named exactly the same and no joy

Kind Regards

Re: group by date SQL Qlik Sense

ogster1974 — Fri, 02 Oct 2015 11:54:56 GMT

Bring up your data model in model view to confirm the join select the order calendar and preview the data.

Check you haven't got duplicates in your calendar

Re: group by date SQL Qlik Sense

joeybird — Fri, 02 Oct 2015 12:39:06 GMT

Hiya

I do because they are a date and time in sql.

is there a way of grouping these?

Kind regards

Re: group by date SQL Qlik Sense

ogster1974 — Fri, 02 Oct 2015 12:45:42 GMT

yes in your load script when adding your order data create a new field called OrderDate which excludes time (This can be done in your SQL View or Qlik using their respective formatting functions) and build and join your calendar on that new date only field and you will be fine.

Rename your original field orderdatetime so you can use it in your dashboard if you need that granulatity in future charts.

Re: group by date SQL Qlik Sense

JonnyPoole — Fri, 02 Oct 2015 13:06:43 GMT

Joanna this almost certainly do your dates actually having a time component that is hidden by Qlik by default. You see duplications of the days because there is hidden timestamp that is different between the same date.

On your primary load of the date field use the following qlik expression to remove the timestamp part

Date(Floor([DateField])) as Date

If qlik understands your date field as a date or date/time it will assign it a number where the whole number represents a calendar day and the decimal numbers represent hours, minutes etc... (time). the Floor function rounds down and effectively turns the date into just a day based date.

hope it helps

Re: group by date SQL Qlik Sense

joeybird — Fri, 02 Oct 2015 13:19:23 GMT

Hiya

worked a treat thank you xx

Kind Regards

Joeybird