Display previous 7 days from today()

[razvan_brais]

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.

[florentina_doga]

use this in script

aa:

load IterNo(),

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

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

[razvan_brais]

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

[Anonymous]

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!!!

[razvan_brais]

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

Thank you

[harishkumarg]

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

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

Regards

Harish

[Anonymous]

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!!

[razvan_brais]

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?

[sunny_talwar]

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

[razvan_brais]

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.