# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

MVP

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

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

Esteemed Contributor

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

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?

MVP

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

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?

Esteemed Contributor

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

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

MVP

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

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?

Highlighted
Esteemed Contributor

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

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;

id,

"date",

sales

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

(ooxml, embedded labels, table is Feuil1);

left Join(t)

Resident t

Group by Year;

left Join(t)

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 *, RecNo() as row ;

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 !

MVP

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

Something like this

Table:

MonthYear,

Sum(sales) as Sales

Group By id, MonthYear;

MonthName(date) as MonthYear,

sales

FROM

(ooxml, embedded labels, table is Feuil1);

FinalTable:

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;

Esteemed Contributor

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

Wanna share my earnings? haha

You're amazing !