topic Straight table that shows date when cumulative sales crosses 10000, 20000, 30000, etc in QlikView

Straight table that shows date when cumulative sales crosses 10000, 20000, 30000, etc

sbfernandes — Fri, 15 Nov 2024 23:59:22 GMT

Greetings.

I have the below input table that shows sales for each day.

Date	Daily Sales
01-Jan-20	7,472
02-Jan-20	3,743
03-Jan-20	1,970
04-Jan-20	5,278
05-Jan-20	4,810
06-Jan-20	8,051
07-Jan-20	5,136
08-Jan-20	6,349
09-Jan-20	5,194
10-Jan-20	525
11-Jan-20	4,368
12-Jan-20	9,494
13-Jan-20	8,202
14-Jan-20	4,178
15-Jan-20	1,673
16-Jan-20	8,888
17-Jan-20	8,567
18-Jan-20	671
19-Jan-20	9,512
20-Jan-20	4,218
21-Jan-20	5,780

I want to create a straight table that would show

The date on which sales crossed multiples of 10,000
The variance in dates between each crossing of 10,000

My output table would look like below:

Sales Crossed (every 10,000)	Date Crossed	Days between each 10,000 (start date 01-Jan-20)
10,000	02/01/2020	1
20,000	05/01/2020	3
30,000	06/01/2020	1
40,000	08/01/2020	2
50,000	11/01/2020	3
60,000	12/01/2020	1
70,000	13/01/2020	1
80,000	16/01/2020	3
90,000	17/01/2020	1
100,000	19/01/2020	2
110,000	21/01/2020	2

Re: Straight table that shows date when cumulative sales crosses 10000, 20000, 30000, etc

edwin — Sun, 13 Sep 2020 14:20:53 GMT

i wish QV can create new tables on the fly as that is what you need. do the accumulation on the fly, save to a table, and use that new table in a chart. but it cant.

i would propose two solutions for this

1. create an extension that will do the cumulative, determine when the 10K crossings happen and do the date diff between crossings. in javascript, you can effectively create a new table on the fly and do pretty much any manipulation you want to the extent javascript allows. you can then suppress any row and show only the rows you care about

2.a more simplistic solution tweaks the visualization a little. since you will do cumulative using above function, you are forced to keep all rows (all dates) , determine when 10K crossings occur and keep the date, do the date diff in the new crossings see attached

Re: Straight table that shows date when cumulative sales crosses 10000, 20000, 30000, etc

Kushal_Chawda — Sun, 13 Sep 2020 16:00:30 GMT

@sbfernandes try below

let vMultipleNumber = 10000; Data: LOAD Date, [Daily Sales] FROM [https://community.qlik.com/t5/QlikView-App-Development/Straight-table-that-shows-date-when-cumulative-sales-crosses/td-p/1743376] (html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1); T1: Load *, if(Sales_multiple_of_Number<>Peek(Sales_multiple_of_Number),1, rangesum(Peek('Cnt_sales_multiple_of_number'),1)) as Cnt_sales_multiple_of_number; Load *, div(Accum_Daily_Sales,$(vMultipleNumber))*$(vMultipleNumber) as Sales_multiple_of_Number; Load *, if(RowNo()=1,[Daily Sales],RangeSum(Peek('Accum_Daily_Sales'),[Daily Sales])) as Accum_Daily_Sales Resident Data Order by "Date"; Drop Table Data; T2: Load *, if(RowNo()=1 and Sales_multiple_of_Number>=$(vMultipleNumber) or peek(Sales_multiple_of_Number)=0 and Sales_multiple_of_Number>=$(vMultipleNumber),1, if((Sales_multiple_of_Number<>Peek(Sales_multiple_of_Number) and RowNo()>1),Peek(Cnt_sales_multiple_of_number))) as Days_between_sales_mutiple_of_number, if(RowNo()=1 and Sales_multiple_of_Number>=$(vMultipleNumber) or peek(Sales_multiple_of_Number)=0 and Sales_multiple_of_Number>=$(vMultipleNumber),1, if((Sales_multiple_of_Number<>Peek(Sales_multiple_of_Number) and RowNo()>1),1)) as Days_between_sales_Flag Resident T1; Drop Table T1; DROP Fields Cnt_sales_multiple_of_number;

Use the Flag Days_between_sales_Flag if you also want to show accumulated sales till date or sales for that day.

Re: Straight table that shows date when cumulative sales crosses 10000, 20000, 30000, etc

sbfernandes — Tue, 15 Sep 2020 05:48:26 GMT

Thank you. This worked as required.

Re: Straight table that shows date when cumulative sales crosses 10000, 20000, 30000, etc

sbfernandes — Tue, 15 Sep 2020 05:52:35 GMT

Thank you. This works well for multiple dimensions.

Re: Straight table that shows date when cumulative sales crosses 10000, 20000, 30000, etc

edwin — Tue, 15 Sep 2020 19:41:04 GMT

no worries, be careful moving your solution to the script side as it will limit you and may not work once you apply filters.