-- TESTING CATSCAN -- -- -- VARIABLE: { name: "date_value", display: "Date", type: "date", format: "Y-m-d" } -- take the date variable from above and alter it. {% set dayfrom = date_value|date('Y-m-01') %} {% set dayto = date_value|date('Y-m-d') %} -- @dataset true -- @title CATSCAN SELECT ('Bookings') as CATSCAN, (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where salesorders.quotation = 0 and salesorders.orddate >= '{{ dayfrom | date('Y-m-01') }}'and salesorders.orddate <= '{{ dayto | date('Y-m-d') }}' and salesorderdetails.orderno = salesorders.orderno) as MTD, (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where salesorders.quotation = 0 and salesorders.orddate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno) as PYMTD, (SELECT CAST(((SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where salesorders.quotation = 0 and salesorders.orddate >= '{{ dayfrom | date('Y-m-01') }}'and salesorders.orddate <= '{{ dayto | date('Y-m-d') }}' and salesorderdetails.orderno = salesorders.orderno )- (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where salesorders.quotation = 0 and salesorders.orddate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno))/(SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where salesorders.quotation = 0 and salesorders.orddate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno)*100 as DECIMAL(10,2))) as `MTHCHANGE%`, (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where QUARTER(orddate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(orddate) = YEAR('{{dayto | date('Y-m-d')}}')and salesorders.quotation = 0 and salesorders.orddate <= '{{ dayto | date('Y-m-d') }}' and salesorderdetails.orderno = salesorders.orderno) as QTD, (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where QUARTER(orddate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(orddate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR))and salesorders.quotation = 0 and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno) as PYQTD, (SELECT CAST(((SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where QUARTER(orddate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(orddate) = YEAR('{{dayto | date('Y-m-d')}}')and salesorders.quotation = 0 and salesorders.orddate <= '{{ dayto | date('Y-m-d') }}' and salesorderdetails.orderno = salesorders.orderno) - (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where QUARTER(orddate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(orddate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR))and salesorders.quotation = 0 and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno)) / (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where QUARTER(orddate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(orddate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR))and salesorders.quotation = 0 and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno)*100 as DECIMAL(10,2))) as `QTRCHANGE%`, (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where YEAR(orddate) = YEAR('{{dayto | date('Y-m-d')}}') and salesorders.quotation = 0 and salesorders.orddate <= '{{ dayto | date('Y-m-d') }}' and salesorderdetails.orderno = salesorders.orderno) as YTD, (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where YEAR(orddate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and salesorders.quotation = 0 and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno) as PYYTD, (SELECT CAST(((SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where YEAR(orddate) = YEAR('{{dayto | date('Y-m-d')}}') and salesorders.quotation = 0 and salesorders.orddate <= '{{ dayto | date('Y-m-d') }}' and salesorderdetails.orderno = salesorders.orderno) - (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where YEAR(orddate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and salesorders.quotation = 0 and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno)) / (SELECT CAST(sum((unitprice *(quantity ))*-discountpercent)+sum(unitprice *(quantity)) AS DECIMAL(10,2)) FROM salesorderdetails LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno where YEAR(orddate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and salesorders.quotation = 0 and salesorders.orddate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR) and salesorderdetails.orderno = salesorders.orderno) as DECIMAL(10,2))) as `YRCHANGE%` UNION SELECT (select 'Billings'), (select sum(discountedlinetotal * -1) from stocktrans where trandate >= '{{ dayfrom | date('Y-m-01') }}' and stocktrans.trandate <= '{{ dayto | date('Y-m-d') }}' ) as MTD, (select sum(discountedlinetotal * -1) from stocktrans where trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and stocktrans.trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYMTD , (SELECT CAST(((select sum(discountedlinetotal * -1) from stocktrans where trandate >= '{{ dayfrom | date('Y-m-01') }}' and stocktrans.trandate <= '{{ dayto | date('Y-m-d') }}' ) - (select sum(discountedlinetotal * -1) from stocktrans where stocktrans.trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and stocktrans.trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum(discountedlinetotal * -1) from stocktrans where trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `MTHCHANGE%`, (select sum(discountedlinetotal * -1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) as QTD, (select sum(discountedlinetotal * -1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYQTD , (SELECT CAST(((select sum(discountedlinetotal * -1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) - (select sum(discountedlinetotal * -1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum(discountedlinetotal * -1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `QTRCHANGE%`, (select sum(discountedlinetotal * -1) from stocktrans where YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) as YTD, (select sum(discountedlinetotal * -1) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYYTD , (SELECT CAST(((select sum(discountedlinetotal * -1) from stocktrans where YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) - (select sum(discountedlinetotal * -1) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum(discountedlinetotal * -1) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `YRCHANGE%` UNION SELECT (select 'Cost'), (select sum(costtotal*-1) from stocktrans where trandate >= '{{ dayfrom | date('Y-m-01') }}' and stocktrans.trandate <= '{{ dayto | date('Y-m-d') }}' ) as MTD, (select sum(costtotal*-1) from stocktrans where trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and stocktrans.trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYMTD , (SELECT CAST(((select sum(costtotal*-1) from stocktrans where trandate >= '{{ dayfrom | date('Y-m-01') }}' and stocktrans.trandate <= '{{ dayto | date('Y-m-d') }}' ) - (select sum(costtotal*-1) from stocktrans where stocktrans.trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and stocktrans.trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum(costtotal*-1) from stocktrans where trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `MTHCHANGE%`, (select sum(costtotal*-1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) as QTD, (select sum(costtotal*-1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYQTD , (SELECT CAST(((select sum(costtotal*-1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) - (select sum(costtotal*-1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum(costtotal*-1) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `QTRCHANGE%`, (select sum(costtotal*-1) from stocktrans where YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) as YTD, (select sum(costtotal*-1) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYYTD , (SELECT CAST(((select sum(costtotal*-1) from stocktrans where YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) - (select sum(costtotal*-1) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum(costtotal*-1) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `YRCHANGE%` UNION SELECT (select 'Margin'), (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where trandate >= '{{ dayfrom | date('Y-m-01') }}' and stocktrans.trandate <= '{{ dayto | date('Y-m-d') }}' ) as MTD, (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and stocktrans.trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYMTD , (SELECT CAST(((select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where trandate >= '{{ dayfrom | date('Y-m-01') }}' and stocktrans.trandate <= '{{ dayto | date('Y-m-d') }}' ) - (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where stocktrans.trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and stocktrans.trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where trandate >= DATE_ADD(( '{{ dayfrom | date('Y-m-01') }}'), INTERVAL -1 YEAR) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `MTHCHANGE%`, (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) as QTD, (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYQTD , (SELECT CAST(((select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) - (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where QUARTER(trandate) = QUARTER('{{dayto | date('Y-m-d')}}') and YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `QTRCHANGE%`, (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) as YTD, (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR)) as PYYTD , (SELECT CAST(((select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where YEAR(trandate) = YEAR('{{dayto | date('Y-m-d')}}')and trandate <= ('{{dayto | date('Y-m-d')}}')) - (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))) / (select sum((discountedlinetotal*-1) - (costtotal*-1)) from stocktrans where YEAR(trandate) = YEAR(DATE_ADD(('{{dayto | date('Y-m-d')}}'), INTERVAL -1 YEAR)) and trandate <= DATE_ADD(('{{ dayto | date('Y-m-d') }}'), INTERVAL -1 YEAR))as DECIMAL(10,2))) as `YRCHANGE%`

Preview Save Changes Cancel

Preview Changes hide