Extract daily data from cumulative data table (today - yesterday)

[Anonymous]

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.

[felipedl]

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:

[sunny_talwar]

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;

[Anonymous]

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.

[Anonymous]

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.

[sunny_talwar]

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;

[Anonymous]

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.

[sunny_talwar]

I understand the first value to be cumulative... but where are you seeing the last value to be cumulative?

[Anonymous]

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.

[sunny_talwar]

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;