topic Re: Set Analysis with variable and date period greater than less than in QlikView

Set Analysis with variable and date period greater than less than

limfungkeat — Sat, 16 Nov 2024 04:09:45 GMT

Dear Masters out there !

I have 2 variables v_DateFrom and v_DateTo, chosen/assigned from Calendar object . Then i need to count total man hrs worked for a list of employee within that date range . The below is not working in my expression . Please assist

Sum({<[EMP_NM]= {'ABC','XYZ'},APPROVEDDATE = {">=$(=Date(v_DateFrom))<=$(=(v_DateTo))"}>}MANHRS)

Thanks !

Lim

Re: Set Analysis with variable and date period greater than less than

Anil_Babu_Samineni — Fri, 15 Mar 2019 02:23:47 GMT

Perhaps this
Sum({<[EMP_NM]= {'ABC','XYZ'},APPROVEDDATE = {">=$(=Date($(v_DateFrom)))<=$(=($(v_DateTo)))"}>}MANHRS)

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Fri, 15 Mar 2019 02:34:43 GMT

Errmmm, thanks loveisfail , the expression show OK , but i do not see any result . It suppose to be some data shown

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Fri, 15 Mar 2019 04:35:14 GMT

Hi @limfungkeat,

=Sum({<APPROVEDDATE ={">=$(=v_DateFrom)<=$(=v_DateTo)"},[EMP_NM]={ 'ABC','XYZ'}>} MANHRS)

Try this, it should work. And in variable itself make it Date(v_DateFrom) rather in set analysis.

- Shubham

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Fri, 15 Mar 2019 06:25:26 GMT

Dear all / Shubham

i attached my qvw file and the data source i extracted from my oracle table . The replies given so far is still not working ...Looking forward to some solutions Thanks !

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Fri, 15 Mar 2019 07:35:42 GMT

It is working in my case, try it

=sum({<vDate = {">=$(=vDateFrom)<=$(=vDateTo)"},EMP_NM={'A','B'}>}MANHRS)

It is creating issue in set analysis because of APPROVEDDATE date format, I changed it then also it wasn't working.

It works in simple exp like =sum(if(APPROVEDDATE>=vDateFrom and appDates<=vDateTo,MANHRS)) but not in set analysis.

So create vDate variable for APPROVEDDATE - vDate =date(APPROVEDDATE)

Regards,

Shubham

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Fri, 15 Mar 2019 08:16:06 GMT

EMP_NM	MANHRS	APPROVEDDATE
A	8	01/15/2019 15:34:00
A	8	02/15/2019 15:34:01
A	8	03/01/2019 15:34:01
A	8	03/15/2019 15:34:02
A	4	03/18/2019 15:34:02

Hi Shubham - thank you so much for proactively help to resolve my problem , refer the above data set , by using the below exp from you (where i have created the variable vDate =date(APPROVEDDATE) )

=sum({<vDate = {">=$(=vDateFrom)<=$(=vDateTo)"},EMP_NM={'A'}>}MANHRS)

so, when i select the date from the calendar object - say 1/3/2018 - 31/3/2018 , i am expecting my pivot table to show

only 20 manhrs , instead of 36 manhrs ...

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Fri, 15 Mar 2019 08:56:40 GMT

HI Limfungkeat,

If you mention EMP='A' then it doesn't work. If you want to show this in table according to EMP name then it will work, I have changed some values in data and look at the result in table.

Now according to above data and if I select date from - 01-03-2019 to 02-03-2019, then it should show 20.

Check and let me know.

-Shubham

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Fri, 15 Mar 2019 09:22:03 GMT

can i have your version of the data source and the qvw file ? I will check and compare against mine . thanks

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Fri, 15 Mar 2019 09:40:47 GMT

Hi,

Sorry I cannot share qvw, but have a look on my exp. Reason why direct date is not working with set analysis because your time is different for same dates. We need to use Floor() for the same.

Script :

LOAD EMP_NM, 
     MANHRS, 
     //APPROVEDDATE as appDates
     Floor(APPROVEDDATE) as appDates
FROM
[C:\Users\KT3028\Downloads\timesheet.xls]
(biff, embedded labels, table is [Sheet 1$]);

Variables :

vDateFrom = appDates
vDateTo = appDates

Now use this expression with dimension EMP_NM,

= sum({<appDates = {">=$(=vDateFrom)<=$(=vDateTo)"}>}MANHRS)

-Shubham

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Fri, 15 Mar 2019 09:56:08 GMT

Reason why it was not working previously with condition EMP_NM={'A'} because same date has different time, so floor() manipulated that.

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Sun, 17 Mar 2019 09:32:57 GMT

thank you so much Shubham ! I got the tips and solution from you man ...

Now , i need to apply this to the data source where i need to run the sql statement and extract the data from Oracle table , instead of using the xls table/ data

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Mon, 18 Mar 2019 04:37:56 GMT

Great, Kudos !!

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Mon, 18 Mar 2019 08:23:48 GMT

Dear Shubham - I tried to apply the same concept by replacing the load statement with the below (am using oracle table as datasource 😞

select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates

FROM timesheet

the output of the straight table is blank .

How to use the Floor function in oracle ? As if I will to re-write the statement like this :

select EMP_NM as employeename, MANHRS, FLOOR(APPROVEDDATE) as appDates

FROM timesheet

I will get error ORA 00932 – Inconsistent data type. Expected number got Date

The data type for APPROVEDDATE in the table is DATE

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Mon, 18 Mar 2019 08:45:53 GMT

Hi,

You need to use Resident load for the same,

oracleScript:
select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates FROM timesheet ;
NoConcatenate
Load EMP_NM as employeename, MANHRS, floor(APPROVEDDATE) as appDates
Resident oracleScript;
Drop table oracleScript ;

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Mon, 18 Mar 2019 09:08:25 GMT

the revised script as below :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT32 TO [eForm Prod;DBQ=PHARMADB ] (XUserId is aABGUCZIPSUaEND, XPassword is EWUBEYFMTbcOXXJOELSA);

oracleScript:

select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates FROM timesheet_npd ;

NoConcatenate

Load EMP_NM as employeename, MANHRS, floor(APPROVEDDATE) as appDates

Resident oracleScript;

Drop table oracleScript ;

LOAD EMP_NM,

MANHRS,

//APPROVEDDATE as appDates

Floor(APPROVEDDATE) as appDates

FROM

[C:\Backup\QlikView\My QlikviewBR\timesheet.xls]

(biff, embedded labels, table is [Sheet 1$]);

and I am getting the error while reloading the script as :

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Mon, 18 Mar 2019 09:14:50 GMT

Ohh Sorry...due to EMP_NM as employeename, it won't recognize EMP_NM in resident load.
Load only EMP_NM only (without alias name) or use employeename in resident load.
Apply same thing to other fields also.

-Shubham

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Mon, 18 Mar 2019 09:33:36 GMT

Changed to the below :

oracleScript:

select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates FROM timesheet_npd ;

NoConcatenate

Load EMP_NM,MANHRS,floor(APPROVEDDATE) as appDates

Resident oracleScript;

Drop table oracleScript ;

and still getting this :

Re: Set Analysis with variable and date period greater than less than

Shubham_Deshmukh — Mon, 18 Mar 2019 09:38:12 GMT

I meant it like below,

oracleScript:
select EMP_NM, MANHRS,APPROVEDDATE FROM timesheet ;
NoConcatenate
Load EMP_NM as emplyeenames, MANHRS, floor(APPROVEDDATE) as appDates
Resident oracleScript;
Drop table oracleScript ;

Re: Set Analysis with variable and date period greater than less than

limfungkeat — Tue, 19 Mar 2019 01:59:00 GMT

Fantastic Shubham ! Thanks a lot man