Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
limfungkeat
New Contributor III

Set Analysis with variable and date period greater than less than

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

 

Labels (3)
1 Solution

Accepted Solutions
Shubham_Deshmukh
Valued Contributor

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

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

 

24 Replies

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

Perhaps this
Sum({<[EMP_NM]= {'ABC','XYZ'},APPROVEDDATE = {">=$(=Date($(v_DateFrom)))<=$(=($(v_DateTo)))"}>}MANHRS)
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
limfungkeat
New Contributor III

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

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

Shubham_Deshmukh
Valued Contributor

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

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

limfungkeat
New Contributor III

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

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 !

 

Shubham_Deshmukh
Valued Contributor

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

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

limfungkeat
New Contributor III

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

EMP_NMMANHRSAPPROVEDDATE
A801/15/2019 15:34:00
A802/15/2019 15:34:01
A803/01/2019 15:34:01
A803/15/2019 15:34:02
A403/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 ...

 

Shubham_Deshmukh
Valued Contributor

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

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.

adad.png

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

yyyyyy.png

Check and let me know.

 

-Shubham

limfungkeat
New Contributor III

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

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

Shubham_Deshmukh
Valued Contributor

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

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