group by date SQL Qlik Sense

[joeybird]

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

04/04/2015

04/04/2015

04/04/2015

06/04/2015

06/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

[ogster1974]

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

[ogster1974]

This should get you started on the prinicples

Understanding the Master Calendar (video)

[joeybird]

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

[ogster1974]

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.

[joeybird]

Hiya

both fields are named exactly the same and no joy

Kind Regards

[ogster1974]

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

[joeybird]

Hiya

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

is there a way of grouping these?

Kind regards

[ogster1974]

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.

[JonnyPoole · Accepted solution]

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