Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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
Sokkorn
Master
Master

Hi Carel,

Try this

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

Make sure your [DATE] is number format.

Regards,

Sokkorn

Gysbert_Wassenaar

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
tresesco
MVP
MVP

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.

Not applicable
Author

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)


Not applicable
Author

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.

Not applicable
Author

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.

Gysbert_Wassenaar

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
Not applicable
Author

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)



jagannalla
Partner - Specialist III
Partner - Specialist III

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