Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
TBQlikNewbi
Contributor II
Contributor II

Count/List of Dates in current fiscal year/quarter

I have a fact table (Activity), and a TimeDimension table.  I need a list and then a count of all the sundays in the current fiscal year in my app.  Currently, I have the following formula:

=Count (Aggr( Only( { <ActivityDate={">=$(=Timestamp(YearStart(Now(), 0,11)))<=$(=Timestamp(YearEnd(Now(),0,11)))"}> * <ActivityDate= {"=num(weekday(ActivityDate))=0"}> } ActivityDate), ActivityDate))

However, this only gets me Sundays that are in the Activity table, because it is associated to the TimeDimension table by a common field, ActivityDate.  When I change this so that TimeDimension is a stand alone table and not associated with Activity, the formula just hangs indefinitely.   ???

 

1 Solution

Accepted Solutions
TBQlikNewbi
Contributor II
Contributor II
Author

Changed expression to this, and now is OK with free standing table:

 

=Count ({ <the_date={">=$(=Timestamp(YearStart(Now(), 0,11)))<=$(=Timestamp(Now()))"}> * <the_date= {"=num(weekday(the_date))=0"}> } the_date)

View solution in original post

1 Reply
TBQlikNewbi
Contributor II
Contributor II
Author

Changed expression to this, and now is OK with free standing table:

 

=Count ({ <the_date={">=$(=Timestamp(YearStart(Now(), 0,11)))<=$(=Timestamp(Now()))"}> * <the_date= {"=num(weekday(the_date))=0"}> } the_date)