Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

SET ANALYSIS WITH DATES NOT WORKING

CAN SOMEONE PLEASE HELP ME.  I AM NOW AT A POINT WHERE I WANT TO BREAK MY COMPUTER LOL.

WHY DOESNT THIS WANT TO WORK CORRECTLY:

sum( {$<DATE = {'>=$(=weekstart(max(DATE)))<=$(=max(DATE))'}>} timeworked )

IT STILL PULLS ALL THE DATA AND NOT ONLY THIS WEEKS DATA...

i EVEN TRIED ADDING THE NUM() FUNCTION, DOENST WORK EITHER

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Re: SET ANALYSIS WITH DATES NOT WORKING

To begin with the max Date is 30/09/2013 and there's no data for that date. If you select for example 18/09/2013 there is data. But you need to specify the date format YYYY/MM/DD. See attached qvw.


talk is cheap, supply exceeds demand

View solution in original post

17 Replies
Highlighted
Master
Master

Re: SET ANALYSIS WITH DATES NOT WORKING

Hi Carel,

Try this

=Sum({<DATE = {">=$(=Num(WeekStart(Max(Date))))<=$(=Date(Max(Date)))"} >} timeworked)

Make sure your [DATE] is number format.

Regards,

Sokkorn

Highlighted
MVP & Luminary
MVP & Luminary

Re: SET ANALYSIS WITH DATES NOT WORKING

Your caps lock key seems to be broken already. Please refrain from posting until you bought a new keyboard.


talk is cheap, supply exceeds demand
Highlighted
MVP
MVP

Re: SET ANALYSIS WITH DATES NOT WORKING

Try this:

sum( {$<DATE = {">=$(=weekstart(max(DATE)))<=$(=max(DATE))"}>} timeworked )

If doesn't work, check if the DATE field is really in date format or string. Best would be, if you can share a sample app.

Highlighted
Not applicable

Re: SET ANALYSIS WITH DATES NOT WORKING

Try the following:

sum( {$<DATE = {">=$(vWeekStart)<=$(vWeekEnd)"}>} timeworked )

And create a variable that computes the start and end of the week.

If you don't like creating variables try the following syntax:

sum( {$<DATE = {"$(>weekstart(max(date))<$(=max(date)"} timeworked)


Highlighted
Not applicable

Re: SET ANALYSIS WITH DATES NOT WORKING

HI,

Thanks for helping but still not working.

=Sum({<DATE = {'>=$(=Num(WeekStart(Max(DATE))))<=$(=Date(Max(DATE)))'} >} timeworked)

I basically want to only show all transactions in the last week, and use the DATE field to do it.

Highlighted
Not applicable

Re: SET ANALYSIS WITH DATES NOT WORKING

Ha ha Wassenaar.

DATE field is loaded in script with this formuala:

Date(FLOOR([worklog_created]),'DD/MM/YYYY') AS DATE

DATE as integer is showing example 41,535, so I assume it is definitely not a text field.

Highlighted
MVP & Luminary
MVP & Luminary

Re: SET ANALYSIS WITH DATES NOT WORKING

Try formatting the dates: sum( {$<DATE = {'>=$(=date(weekstart(max(DATE))))<=$(=date(max(DATE)))'}>} timeworked ). Perhaps you also need to specify the date format: ...date(max(DATE),'DD/MM/YYYY')...etc

If that doesn't help please post a sample document that demonstrates the problem.

In general it's a good idea to load dates as numbers only without any date format and do the formatting only in the objects on the sheets. That makes it easier to work with dates in set analysis expressions.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: SET ANALYSIS WITH DATES NOT WORKING

Hi,

In Script, calculate the numeric value equivalent to the date field,

floor(num(Date([worklog_created],'DD/MM/YYYY'))) AS DATE_NUM


and write the expression as below in set,


Sum({<DATE_NUM = {'>=$(=floor(Num(WeekStart(Max(Date))))) <=$(=floor(num(Date(Max(Date)))))'} >} timeworked)



Highlighted
Partner
Partner

Re: Re: SET ANALYSIS WITH DATES NOT WORKING

Hi,

Why don't you try first with hard code values i.e. static values inside set analysis.

If it works perfectly then check the formula first in text object. If you are able to see values then integrate in expression.

Please share app with sample real data if you are not able to do.

Thanks,

Jagan