Discussion board where members can get started with Qlik Sense.
I think you might be looking for a solution like this:
The As-Of Table
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?
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?
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?
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?
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:
load *,(month(date)) as "Month",year(date) as Year ,
year(date)*12+month(date) as monthid;
(ooxml, embedded labels, table is Feuil1);
load Year, (LastValue("Month")-1) as LastMonth
Group by Year;
load Year,(LastValue("Month")-2) as "LastMonth-1"
load sum(sales) as salesLast, "Month", Year Resident t Where (LastMonth)=num("Month") Group By Year,"Month";
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
Load * Resident a Order by "Month" desc ;
Drop table a;
my excel source is attached !
THANK you Sunny !
Something like this
Sum(sales) as Sales
Group By id, MonthYear;
MonthName(date) as MonthYear,
If(Sales > Alt(Previous(Sales), 0) and Sales > Alt(Previous(Previous(Sales)), 0), 'Good', 'Bad') as State
Order By id, MonthYear;
DROP Table Table;
Wanna share my earnings? haha
You're amazing !