Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have more questions real time using Qlik. Hope this will help others as well.
I have a table names as 'purchase'.
PURCHASE:
Date Customer_Name Purchase_count (Cumulative)
10-09-2017 John 43056
10-09-2017 Ryan 44000
10-09-2017 Martin 45600
10-10-2017 John 45900
10-10-2017 Ryan 46700
10-10-2017 Martin 47000
10-11-2017 John 47900
10-11-2017 Ryan 48500
10-11-2017 Martin 50000
How can I extract daily data from this table? (Today - yesterday) will give me daily value. I tried using FirstSortedValue(), but of no luck. I have 2 simple questions.
1. Please let me know what is the better way to extract daily values, At the time of loading table or while writing in the expression on chart?
2. Please help me with the script for what ever the better way you suggest.
Thank you.
Or this
Table:
LOAD * INLINE [
Date, Cust, Cum_Purchase_Count
10-09-2017, John, 43056
10-09-2017, Ryan, 44000
10-09-2017, Martin, 45600
10-10-2017, John, 45900
10-10-2017, Ryan, 46700
10-10-2017, Martin, 47000
10-11-2017, John, 47900
10-11-2017, Ryan, 48500
10-11-2017, Martin, 50000
];
FinalTable:
LOAD *
Where Len(Trim(Daily_Purchase_Count)) > 0;
LOAD *,
If(Cust = Previous(Cust), RangeSum(Cum_Purchase_Count, -Previous(Cum_Purchase_Count))) as Daily_Purchase_Count
Resident Table
Order By Cust, Date;
DROP Table Table;
HI,
Using the following script to get your data:
dummyData:
Load * Inline
[
Date,Customer_Name,Purchase_count (Cumulative)
10-09-2017,John,43056
10-09-2017,Ryan,44000
10-09-2017,Martin,45600
10-10-2017,John,45900
10-10-2017,Ryan,46700
10-10-2017,Martin,47000
10-11-2017,John,47900
10-11-2017,Ryan,48500
10-11-2017,Martin, 50000
];
NoConcatenate
Data:
Load
Date#(Date,'MM-DD-YYYY') as Date,
Customer_Name,
[Purchase_count (Cumulative)]
Resident dummyData;
drop table dummyData;
You can use set analysis to check by customer name, what is the daily Purchase count with the expression:
sum({<Date={"$(=Date(max(Date),'MM-DD-YYYY'))"}>}[Purchase_count (Cumulative)])-sum({<Date={"$(=Date(max(Date)-1,'MM-DD-YYYY'))"}>}[Purchase_count (Cumulative)])
By using this, and selecting a date, I got the following:
A purely script based solution will look like this
Table:
LOAD * INLINE [
Date, Cust, Cum_Purchase_Count
10-09-2017, John, 43056
10-09-2017, Ryan, 44000
10-09-2017, Martin, 45600
10-10-2017, John, 45900
10-10-2017, Ryan, 46700
10-10-2017, Martin, 47000
10-11-2017, John, 47900
10-11-2017, Ryan, 48500
10-11-2017, Martin, 50000
];
FinalTable:
LOAD *,
If(Cust = Previous(Cust), RangeSum(Cum_Purchase_Count, -Previous(Cum_Purchase_Count)), Cum_Purchase_Count) as Daily_Purchase_Count
Resident Table
Order By Cust, Date;
DROP Table Table;
Thanks for try but this is confusing with date in set expressions. As the function also Date, I could not replace my date. Can you use some thing like 'newDate' instead of 'Date' in the expression. In that way I can test the expression and let you know. Thanks for reply.
Can you exclude the first and last values in new daily table which are not subtracted from previous values, infact no values available to subtract them. Thanks.
May be this
Table:
LOAD * INLINE [
Date, Cust, Cum_Purchase_Count
10-09-2017, John, 43056
10-09-2017, Ryan, 44000
10-09-2017, Martin, 45600
10-10-2017, John, 45900
10-10-2017, Ryan, 46700
10-10-2017, Martin, 47000
10-11-2017, John, 47900
10-11-2017, Ryan, 48500
10-11-2017, Martin, 50000
];
FinalTable:
LOAD *,
If(Cust = Previous(Cust), RangeSum(Cum_Purchase_Count, -Previous(Cum_Purchase_Count))) as Daily_Purchase_Count
Resident Table
Order By Cust, Date;
DROP Table Table;
Thanks for your reply. What I need is in the Final Table which has daily values, we still get First value and Last Value which are cumulative values. As they do not have any other days to subtract. Is there any way I can Load 'final table' on a condition of, exclude first and last value.
Thanks.
I understand the first value to be cumulative... but where are you seeing the last value to be cumulative?
Correct. Last value should be fine. I want to get ride of that cumulative value. I have tried loading Rowno() along with columns and then limit by excluding it. It did not work. can you please help me in getting cumulative value cleaned from new table which has only daily values. Thank you.
This is not working?
Table:
LOAD * INLINE [
Date, Cust, Cum_Purchase_Count
10-09-2017, John, 43056
10-09-2017, Ryan, 44000
10-09-2017, Martin, 45600
10-10-2017, John, 45900
10-10-2017, Ryan, 46700
10-10-2017, Martin, 47000
10-11-2017, John, 47900
10-11-2017, Ryan, 48500
10-11-2017, Martin, 50000
];
FinalTable:
LOAD *,
If(Cust = Previous(Cust), RangeSum(Cum_Purchase_Count, -Previous(Cum_Purchase_Count))) as Daily_Purchase_Count
Resident Table
Order By Cust, Date;
DROP Table Table;