Hello Qlik Sense Community,
We have problems with setting up the Date picker widget. We have a table chart report.
When we clear the date period selection we see all the rows in the report. When we select dates some of the records disapear. We want to change it so we see all the rows no matter the period was selected. The rows are the items movements during the years.
So the first task we need to solve is how to setup the widget so it doesn't affect the number of rows?
There is no answered question we can find in the forum and stackoverflow.
Here is explanation of the report we are trying to achieve.
From the REVISION table we take the last quantity in the store. Then we add the sum of the deliveries during the period and subtract the sales. This is how we get the quantity at the end of the period.
We do know that there is additional condition how the dates are positioned compared to the date of the montly REVIEW.
But we still don't know how to take the start and end period of the selected period.
So the second question is how to use the Start and End date of the period? What are the names, how do we define/change the default names?
Another option is to use another date table and choose the dates from it. But then how we are going to filter the sales/deliveries during the chosen period? And how we are going to select the most recent Review-date? How we compare with start-end of period?
Here are the date fields that we see that somehow work, but still hides rows: sold_date, delivery_date, review_date
The loading of the tables is as follow below:
ITEMS:
LOAD
ID as i_item_id,
ID as review_item_id,
ID as sales_item_id,
ID as delivery_item_id,
CODE as i_code,
NAME as i_name,
BARCODE as i_barcode,
ISACTIVE as i_is_active;
SQL SELECT ID,
ISACTIVE,
CODE,
NAME,
BARCODE
FROM ITEMS WHERE ISACTIVE>0;
REVISIONS:
LOAD
ID as review_ID,
REVIEWMONTH as REVIEW_MONTH,
REVIEWYEAR as REVIEW_YEAR,
MakeDate(REVIEWYEAR,REVIEWMONTH,1) AS review_start_date,
ITEMID as review_item_id,
AVLQUA as review_available_quantity,
ISACTIVE as review_is_active;
SQL SELECT ID,
ISACTIVE,
REVIEWMONTH,
REVIEWYEAR,
ITEMID,
AVLQUA
FROM STROBJECTS where ISACTIVE>0;
SALES:
LOAD ID,
ITEMID as sales_item_id,
ApplyMap ('ITEMS_MAP', ITEMID) as sales_item_name,
QUANTITY as sold_quantity,
DELIVERYDATE as sold_date,
ISACTIVE as sales_ISACTIVE;
SQL SELECT ID,
ITEMID,
QUANTITY,
DELIVERYDATE,
ISACTIVE
FROM STRCONTENT225 where ISACTIVE>0;
DELIVERIES:
FIRST 10000
LOAD ID as dc_id,
ITEMID as delivery_item_id,
DELIVERYDATE as delivery_date,
QUANTITY as delivery_quantity,
SECONDQUANTITY as delivery_second_quantity,
ISACTIVE as delivery_ISACTIVE;
SQL SELECT ID,
ITEMID,
DELIVERYDATE,
QUANTITY,
SECONDQUANTITY,
ISACTIVE
FROM STRCONTENT3 WHERE ISACTIVE>0;
/// this is the auto calendar generated by the DatePicker widget:
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [REVIEW_SROK], [REVIEW_DELIVERYDATE], [sold_date], [delivery_date] USING [autoCalendar] ;
The formula for the quantity that give us (wrong) result is:
=AGGR(SUM(review_available_quantity),i_code) - AGGR(SUM(sold_quantity),i_code)+AGGR(SUM(delivery_quantity),i_code)
We are aware that the first part of the formula
AGGR(SUM(review_available_quantity),i_code)
is not completely correct.
We also wonder how do we get the maximum by date and closest to the period `review_available_quantity`.
Constructions used for similar filtering require values when defining the formula.
Any help is appreciated. Thanks in advance!
