Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sbfernandes
Contributor III
Contributor III

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

Greetings.

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

DateDaily Sales
01-Jan-207,472
02-Jan-203,743
03-Jan-201,970
04-Jan-205,278
05-Jan-204,810
06-Jan-208,051
07-Jan-205,136
08-Jan-206,349
09-Jan-205,194
10-Jan-20525
11-Jan-204,368
12-Jan-209,494
13-Jan-208,202
14-Jan-204,178
15-Jan-201,673
16-Jan-208,888
17-Jan-208,567
18-Jan-20671
19-Jan-209,512
20-Jan-204,218
21-Jan-205,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 CrossedDays between each 10,000 (start date 01-Jan-20)
10,00002/01/20201
20,00005/01/20203
30,00006/01/20201
40,00008/01/20202
50,00011/01/20203
60,00012/01/20201
70,00013/01/20201
80,00016/01/20203
90,00017/01/20201
100,00019/01/20202
110,00021/01/20202
Labels (2)
2 Solutions

Accepted Solutions
edwin
Master II
Master II

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

View solution in original post

Kushal_Chawda

@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.

Screenshot 2020-09-13 165338.png

View solution in original post

5 Replies
edwin
Master II
Master II

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

Kushal_Chawda

@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.

Screenshot 2020-09-13 165338.png

sbfernandes
Contributor III
Contributor III
Author

Thank you. This worked as required.

sbfernandes
Contributor III
Contributor III
Author

Thank you. This works well for multiple dimensions.

edwin
Master II
Master II

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