Qlikview script

[Anonymous]

Guys,

I have a date field by month wise having worked hours for some employees in excel sheet. I want to take the worked hours for each employee by month wise. But here a single date can have multiple rows or values in excel. so how to use group by in script for month wise? plz help me..

[Colin-Albert]

Your filed is holding a time value so you are getting each unique datetime value rather than each date.

Use FLOOR() to round the date to an integer and get a single value in your load script.

Use something like  date(floor(sheet_date)) as sheet_date.

You can also use the month() or monthname() functions to extract the month or year & month.

[Anonymous]

But I'm having same time for all the dates, Colin

[maxgro]

could you post some rows of your excel?

[Colin-Albert]

How many values does a listbox in your Qlik app on the date field show?

What other fields are in your export? - Other fields may be causing the multiple values to be shown.

[Anonymous]

I want to group the hours of an employee by month wise, but it should not take distinct dates, it should include all the dates.

[Anonymous]

[Colin-Albert]

do not include the timesheet id in your table this is causing the export to have each row rather than the sum by month.

Why don't you create the chart directly in QlikView? Use a Chart object  with proj_id and monthname(sheetdate) as dimensions and use sum(worked_hours) as your expression. It looks like you are using a table object rather than a Straight Table chart.

[kamielrajaram]

Hi,

Why not add a month and year column to your table.

Year(sheet_date),

Month(sheet_date)

Then add year and month in your dimension and sum(hours_worked)

Kind Regards

Kamiel

[maxgro]

I think you can use a chart with

calculated dimension           MonthStart(sheet_date)

dimension                           employee

expression                          sum(worked_houes)