Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:

The As-Of Table

OmarBenSalem
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?

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?

OmarBenSalem
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

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
OmarBenSalem
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)

Capture.PNG

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 !

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

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;

Capture.PNG

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

Community Browser