Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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
@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.
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
@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.
Thank you. This worked as required.
Thank you. This works well for multiple dimensions.
no worries, be careful moving your solution to the script side as it will limit you and may not work once you apply filters.