topic Qlikview script in QlikView

Qlikview script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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..

Re: Qlikview script

Colin-Albert — Mon, 06 Mar 2017 12:36:04 GMT

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.

Re: Qlikview script

Anonymous — Mon, 06 Mar 2017 12:43:05 GMT

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

Re: Qlikview script

maxgro — Mon, 06 Mar 2017 12:46:46 GMT

could you post some rows of your excel?

Re: Qlikview script

Colin-Albert — Mon, 06 Mar 2017 12:48:39 GMT

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.

Re: Qlikview script

Anonymous — Mon, 06 Mar 2017 13:03:38 GMT

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.

Re: Qlikview script

Anonymous — Mon, 06 Mar 2017 13:04:24 GMT

Re: Qlikview script

Colin-Albert — Mon, 06 Mar 2017 13:15:18 GMT

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.

Re: Qlikview script

kamielrajaram — Mon, 06 Mar 2017 13:17:33 GMT

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

Re: Qlikview script

maxgro — Mon, 06 Mar 2017 13:25:40 GMT

I think you can use a chart with

calculated dimension MonthStart(sheet_date)

dimension employee

expression sum(worked_houes)