Compare count of this week vs last week

[peterderrington]

Hi There, 

I'm hoping this will be a fairly easy one for you guys.

I'm trying to create a simple straight table that shows the count of compliance against the count for 7 days ago (ultimately i then want to be able to show a little up green or red down arrow next to it)

I can see the current count with this expression:

Count([Name])-Count({<[Name]={"Unknown"}>}[Name])

But when i try this (which i thought it might be) it correctly shows the date as 7 days prior but the count is empty.

=count(>=$(=(Date(Today()-7)))
=Count([Name])-Count({<[Name]={"Unknown"}>}[Name])

[tincholiver]

did you try this?

count({<Date={'>=$(=(Date(Today()-7))<$(=(Date(Today())'}>}Name)

[dplr-rn]

You set analysis syntax is wrong
try something like below
Last 7 days count({YOURDATEFIELD={">=$(=Today()-7) "}>}}Name)
14-7 days count({YOURDATEFIELD={"<$(=Today()-7) >=$(=Today()-14)"}>}}Name)

[peterderrington]

No, that hasn't worked. I get a red line after the first = count({[Date/Time left]={">=$(=Today()-7) "}>}}[Name])

[dplr-rn]

sorry think i made a typo added an extra } and missed <
try this
Last 7 days count({<YOURDATEFIELD={">=$(=Today()-7) "}>}Name)
14-7 days count({<YOURDATEFIELD={"<$(=Today()-7) >=$(=Today()-14)"}>}Name)

[dplr-rn]

hope it worked.
if not please share some sample data
if yes. please mark and answered and close the thread

[tincholiver]

If you need to compare weeks maybe it suits you to use
count ({<NumWeek = {'> = $ (= max (NumWeek) -1) <$ (= max (NumWeek))'}>} Name)

NumWeek is the week number that corresponds to a range of dates and you can get that value from the master calendar with the following line:

num (Week (yourdates), '00') AS NumWeek,

[peterderrington]

So that hasn't worked unfortunately. I think the problem is possibly in that first date field. In my initial expression it returns the correct date in the label (which i've just left as ) so i know that that expression is looking at the right date but i get an 'Errors in expression' in the 'Edit Expression' Box (its highlighting the first open bracket in Red). I know the second element of my expression is also correct as this does count the number of times a Name appears in the list (and doesn't count the 'unknown' entries). So my presumption is that there's an error in linking the two expressions. (and also probably a formatting issue with the date field). Data looks similar to this PDate Name 22/03/2018 Fred 22/03/2018 Unknown 22/03/2018 Bill 22/03/2018 Bill 22/03/2018 Claire 22/03/2018 John

[peterderrington]

ok...... i've never done anything with the master calendar. Where would i edit these elements?

[tincholiver]

Hi Peter,

The master calendar is a table that contains all the dates of the one year and all the following that you want. That way you link the field dates of your table with the table of the calendar and thus you will obtain all the months, years semes, trimenestres, cuatrimestres, etc. Copy and paste the next script in a new qlikview document, run the script and load in your app, make sure that the format date its the same that you have in your tables:

The scipt to generate this is the next:

FOR i = 2018 TO 2028 //Put here the initial year that you want to star the calendar to the last one

LET curYear = $(i);
LET StartDate = makedate(curYear); 
LET YearEnd = yearend(StartDate);
LET TotalDays = (YearEnd - StartDate) + 2; 

TempCalendar: 
LOAD
date('$(StartDate)' + recno() - 1) AS PeriodDate
AUTOGENERATE(TotalDays - 1);


Next

Calendario:
Load
PeriodDate                                                                               AS     %DATE,
Year(PeriodDate)                                                                   AS    YEAR,
num(month(PeriodDate))                                                 AS   MONTH,                     //Number format
DUAL(text(date(PeriodDate,'MMM')),num(month(PeriodDate))) AS MES, //Alfabeth format
DUAL(date(PeriodDate,'MMM') & ' ' & date(PeriodDate,'yyyy'),
Year(PeriodDate)*100+NUM(Month(PeriodDate))) AS MES_AÑO,                // Month and year
WeekDay(PeriodDate)                                                        AS WEEKDAY,
num(Week(PeriodDate),'00')                                          AS WEEK,                          //Number format
num(Day(PeriodDate),'00')                                              AS DAY,
CEIL(Num(Month(PeriodDate))/3)&'°T'                      AS TRIM,                           //Quarter
num(weekday(PeriodDate)) AS DIA_NRO,
if(num(weekday(PeriodDate)=6) OR num(weekday(PeriodDate)=0),0,1) AS WORKINGDAY
Resident TempCalendar;

STORE Calendario into C:\Nube\Wicham\Qapacitacion\Practicas\Calendario\CalendarioLargo.qvd(qvd);
DROP TABLE Calendario;
DROP TABLE TempCalendar;

Let me know if it helps you, another way share some data to solve your problem