creating a single interval (min date-max date) from a list of intervals depending on a situation

[OmarBenSalem]

Hi all,

my demand is rather simple,

I have an excel file :

as you can see, the id 1 has situation A from 10/01/2017 to 13/01/2017 then from 17/01/2017 to 18/01/2017
a situation B from 14/01/2017 to 16/01/2017

So I want to be able to limit the intervals to those listed above depending on the change of the situation:

here is what I've did:

test:

Load

id,

situation,

date(min(debut)) as MinDate,

date(max(fin)) as MaxDate

Group by id,situation;

LOAD

  id,

    debut,

    fin,

    situation

FROM [lib://source/source.xlsx]

(ooxml, embedded labels, table is Feuil1);

this was the result :

Now, the question is, how can I seperate 10/01 to 13/01 and 17/01 to 18/01 ?
Thanks !

[sunny_talwar]

I think you might be looking for a solution like this:

The As-Of Table‌

[OmarBenSalem]

Sorry to keep bothering you, but I'm in charge of a Big project and I'm getting really confused...

I can't find out a way to overcome this issue!

I have a question though,

If(sum(salesLast)>= sum("salesLast-1"),'En hausse','En Baisse') as etat;

why is this keeps on showing errors?

[sunny_talwar]

I just saw an attached Excel, what is the output you expect to see from the attached Excel? A new Column with Good/Bad? and you care about the Month of date?

[OmarBenSalem]

You're one of a kind sunny ))

In fact, I have dates with sales, I want to be able to track the sales state of our customers.

Wich means, if a customer A, have has sales in Jan=500 ,Feb=800 (and we're currently in Mars)

I want to generate a table, which contains 3 columns:

customer | Month | Year |State

A             | Feb     | 2017 | Good (since sales for feb> sales for jan)

this table will be concatenated each time with the same piece of information for every year to guard a history.

Hope this is clear?

Thanks

[sunny_talwar]

Hahahaha clear but I still have questions

1) Is id = customer here?

2) You have date in your Excel, do you Sum(sales) to get sales for the Month?

3) Your final table will have customer, month, year, state and may be sum(sales)? rest all information isn't needed?

[OmarBenSalem]

1) Yes, id = customer

2) here is how my source is : (so yes, sum(sales) to get sales for a month)

My final table will have only: customer, monthYear or (month and year) depends on how we work out the solution and the state : good or bad !

and this is what I've done so far:

t:

load *,(month(date)) as "Month",year(date) as Year ,

year(date)*12+month(date) as monthid;

LOAD

    id,

    "date",

    sales

FROM [lib://source/etat.xlsx]

(ooxml, embedded labels, table is Feuil1);

left Join(t)

load Year, (LastValue("Month")-1) as LastMonth

Resident t

Group by Year;

left Join(t)

load Year,(LastValue("Month")-2) as "LastMonth-1"

Resident t

Group by Year;

a:

load sum(sales) as salesLast, "Month", Year Resident t Where (LastMonth)=num("Month") Group By Year,"Month";

Concatenate

load sum(sales) as "salesLast-1", "Month", Year Resident t Where ("LastMonth-1")=num("Month") Group By Year,"Month";

drop table t;

load Month,Year,etat Where row=1;

load *, RecNo() as row ;

load ("Month"), "Year" ,

If((salesLast)>= ("salesLast-1"),'En hausse','En Baisse') as etat

;

final:

Load * Resident a Order by "Month" desc ;

Drop table a;

my excel source is attached !

THANK you Sunny !

[sunny_talwar]

Something like this

Table:

LOAD id,

  MonthYear,

  Sum(sales) as Sales

Group By id, MonthYear;

LOAD id,

    MonthName(date) as MonthYear,

    sales

FROM

[..\..\Downloads\etat.xlsx]

(ooxml, embedded labels, table is Feuil1);

FinalTable:

LOAD *,

  If(Sales > Alt(Previous(Sales), 0) and Sales > Alt(Previous(Previous(Sales)), 0), 'Good', 'Bad') as State

Resident Table

Order By id, MonthYear;

DROP Table Table;

[OmarBenSalem]

Wanna share my earnings? haha

You're amazing !