topic Re: Display previous 7 days from today() in QlikView

Display previous 7 days from today()

razvan_brais — Wed, 25 Nov 2020 16:16:04 GMT

Hy guys.

I would like to show in a table last 7 days calculated from today.

So , my table I would like to look something like this:

Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday(today)
21/09/2016	22/09/2016	23/09/2016	24/09/2016	25/09/2016	26/09/2016	27/09/2016

And I would like to have the option to select them to filter after the date I selected.

How can I do this?

Thank you.

Re: Display previous 7 days from today()

florentina_doga — Tue, 27 Sep 2016 12:54:45 GMT

use this in script

aa:

load IterNo(),

date(today()-IterNo()+1,'DD-MMM-YYYY') as date

AutoGenerate(1) while IterNo()<=7;

Re: Display previous 7 days from today()

razvan_brais — Tue, 27 Sep 2016 13:01:26 GMT

Hy Florentina. That it`s okay just for generating those 7 days . But in my app , i have info for a period. What I would like is to create a table , based on my date column from my table. And where I can have the possibility to select a date.

Thanks

Re: Display previous 7 days from today()

Anonymous — Tue, 27 Sep 2016 13:11:03 GMT

Hi Razvan:

SET DayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LET vMin = Num(Today()-6);
LET vMax = Num(Today());
LET vFinal = '';

Aux:
LOAD
    Date($(vMin)+IterNo()-1) as Date,
    WeekDay($(vMin)+IterNo()-1) as DayName
AutoGenerate 1
While $(vMin) + IterNo() -1 <= $(vMax);

FOR i = 0 TO NoOfRows('Aux')-1
    LET vDay = Peek('Date',$(i),'Aux');
    IF Peek('Date',$(i),'Aux') = Today() Then
        LET vDayName = '['&Peek('DayName',$(i),'Aux')&' (today)'&']';
    ELSE
        LET vDayName = '['&Peek('DayName',$(i),'Aux')&']';
    END IF;
    IF $(i) = 0 THEN
        TABLE:
        LOAD
            Date('$(vDay)') as $(vDayName)
        AutoGenerate 1;   
    ELSE
        Concatenate(TABLE)
        LOAD
            Date('$(vDay)') as $(vDayName)
        AutoGenerate 1;   
    END IF;
    IF $(i)>0 AND $(i) < NoOfRows('Aux')-1 THEN
        LET vFinal = '$(vFinal)'&'SUM($(vDayName)) as $(vDayName),';
    ELSEIF $(i)=0 THEN
        LET vFinal = 'SUM($(vDayName)) as $(vDayName),';
    ELSE
        LET vFinal = '$(vFinal)'&'SUM($(vDayName)) as $(vDayName)';
    END IF;
Next i;

LET vFinal = 'NoConcatenate FINAL_TABLE: LOAD $(vFinal) RESIDENT TABLE';


$(vFinal);

If you want to change the today option, change vMin and vMax vars.

Regards!!!

Re: Display previous 7 days from today()

razvan_brais — Tue, 27 Sep 2016 13:16:40 GMT

Hy Manuel , and how can I use this script with my Other tables that have a column for date?

Thank you

Re: Display previous 7 days from today()

harishkumarg — Tue, 27 Sep 2016 13:22:39 GMT

Try something in set analysis, (in case you are using any aggregation function)

only({<Date={">=$(=Date(Today()-7))<=$=(Date(Today()))"}>}[Field Name])

Regards

Harish

Re: Display previous 7 days from today()

Anonymous — Tue, 27 Sep 2016 13:24:25 GMT

Change this code:

LET vMin = Num(Today()-6);

LET vMax = Num(Today());

LET vFinal = '';

By this code

MinMaxDates:

LOAD

Min(Date) as MinDate

Max(Date) as MaxDate

RESIDENT YourTable;

LET vMin = Num(Peek('MinDate',0,'MinMaxDates');

LET vMax = Num(Peek('MaxDate',0,'MinMaxDates');

LET vFinal = '';

Regards!!

Re: Display previous 7 days from today()

razvan_brais — Tue, 27 Sep 2016 13:34:56 GMT

MinMaxDates:

LOAD

Min(BALANCE_DATE) as MinDate,

Max(BALANCE_DATE) as MaxDate

RESIDENT Balanta;

LET vMin = Num(Peek('MinDate',0,'MinMaxDates');

LET vMax = Num(Peek('MaxDate',0,'MinMaxDates');

LET vFinal = '';

Aux:

LOAD

Date($(vMin)+IterNo()-1) as Date,

WeekDay($(vMin)+IterNo()-1) as DayName

AutoGenerate 1

While $(vMin) + IterNo() -1 <= $(vMax);

FOR i = 0 TO NoOfRows('Aux')-1

LET vDay = Peek('Date',$(i),'Aux');

IF Peek('Date',$(i),'Aux') = Today() Then

LET vDayName = '['&Peek('DayName',$(i),'Aux')&' (today)'&']';

ELSE

LET vDayName = '['&Peek('DayName',$(i),'Aux')&']';

END IF;

IF $(i) = 0 THEN

TABLE:

LOAD

Date('$(vDay)') as $(vDayName)

AutoGenerate 1;

ELSE

Concatenate(TABLE)

LOAD

Date('$(vDay)') as $(vDayName)

AutoGenerate 1;

END IF;

IF $(i)>0 AND $(i) < NoOfRows('Aux')-1 THEN

LET vFinal = '$(vFinal)'&'SUM($(vDayName)) as $(vDayName),';

ELSEIF $(i)=0 THEN

LET vFinal = 'SUM($(vDayName)) as $(vDayName),';

ELSE

LET vFinal = '$(vFinal)'&'SUM($(vDayName)) as $(vDayName)';

END IF;

Next i;

LET vFinal = 'NoConcatenate FINAL_TABLE: LOAD $(vFinal) RESIDENT TABLE';

This is what i wrote , but it gives me some error. Something like :

Field not found <<=> . Any idea?

Re: Display previous 7 days from today()

sunny_talwar — Tue, 27 Sep 2016 13:38:21 GMT

I don't understand what the final goal is here. Your data come in as the table you have posted above?

Re: Display previous 7 days from today()

razvan_brais — Tue, 27 Sep 2016 13:41:28 GMT

No , that is what I want to achive. But I don`t think that is necessary to create these in script. I`m thinking at this:

current day to be max(DATE) , and for each previous day to be max(DATE)-1 , max(DATE)-2 and so on. But I don`t think this is a good solution because I would like to select one of those dates to help me to caluculate sum for a selected date.

Re: Display previous 7 days from today()

miguelbraga — Tue, 27 Sep 2016 13:50:42 GMT

Hey there,

Check out this document from community:

Selecting Arbitrary Date Ranges

Best regards,

D.A. MB

Re: Display previous 7 days from today()

Anonymous — Tue, 27 Sep 2016 14:04:40 GMT

Could you post an screenshot of this error plz?

Re: Display previous 7 days from today()

razvan_brais — Wed, 28 Sep 2016 10:52:51 GMT

I solved my problem by writing this expression:

if(((DATE>GetFieldSelections(DATE)-7) & (DATE> GetFieldSelections(DATE))),WeekDay(DATE) & ' ' & DATE)