|
Code below is for calculating 'prior period' and 'prior year'..somewhat tricky beause it is based on a primary data filter where user picks a varying range of date values--eg. current year, or 4 months to 3 months ago, or 2 months ago to today,etc.. Code for prior year just picks the starting date and ending date and moves it 1 year back.. plain and simple. The prior period calculates time elapsed (ex 4 to 5 months ago is 30 days) . It takes the startign DAte, offsets it by one day, then calculates back for 30 days (using above example)
q = load \"x_LS_Invoice_Volume\";\nq= filter q by {{row(Date_2.selection,[0],[\"min\",\"max\"]).asDateRange(\"date('Date_Year', 'Date_Month', 'Date_Day')\")}};q1 = foreach q generate 'LOB2' as 'lob2','Account.Type' as 'ty','Date' as 'invdt','Date_sec_epoch' as 'sec',toDate('Date_sec_epoch'- 31470526 ) as 'YrAgo', 'Date_sec_epoch'- 86400 as 'yesterday';\nq2=foreach q1 generate'invdt' as 'invDt','sec' as 'sec', 'YrAgo' as 'YrAgo','yesterday' as 'Yesterday';\nq3=group q2 by all;\nq3=foreach q3 generate min('sec') as 'StartingDtSec',max('sec') as 'EndingDtSec',toDate(min('sec')) as 'StartingDt',toDate(max('sec')) as 'EndingDt', min('YrAgo') as'StartingPrevYrDt',max('YrAgo') as 'EndingPrevYrDt',min('Yesterday') as 'PeriodEnd_seconds',toDate(min('Yesterday')) as 'PeriodEndDate';\nq4 = foreach q3 generate 'StartingDt' as 'StartingDt','StartingDtSec'as 'StartingDtSec','EndingDtSec' as 'EndingDtSec', 'EndingDt' as 'EndingDt', 'StartingPrevYrDt' as 'StartingPrevYrDt', 'EndingPrevYrDt' as 'EndingPrevYrDt','PeriodEnd_seconds' as 'PeriodEnd_seconds', 'PeriodEndDate' as 'PeriodEndDate',date_diff(\"day\",toDate('StartingDtSec'),toDate('EndingDtSec')) as 'delta';\nq5 = foreach q4 generate 'StartingDt' as 'StartingDt', 'EndingDt' as 'EndingDt','StartingDtSec'as 'StartingDtSec','EndingDtSec' as 'EndingDtSec', 'StartingPrevYrDt' as 'StartingPrevYrDt', 'EndingPrevYrDt' as 'EndingPrevYrDt', date_to_epoch('StartingPrevYrDt')as 'StartingPrevYrDt_seconds',date_to_epoch('EndingPrevYrDt')+ 86400 as 'EndingPrevYrDt_seconds', 'PeriodEnd_seconds' as 'PeriodEnd_seconds', 'PeriodEndDate' as 'PeriodEndDate','delta' as 'delta',toDate(date_to_epoch('StartingDt') - 86400 * 'delta' )as 'PeriodStartDt',date_to_epoch('StartingDt') - 86400 * 'delta' as 'PeriodStartDt_seconds';
0 Comments
q1 = filter q by {{column(timeStampText_4.selection,["Snapshot1"]).asEquality('timeStampText')}};
q1 = filter q1 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,"Valu").asString()}}; q2 = filter q by {{column(timeStampText_5.selection,["Snapshot2"]).asEquality('timeStampText')}}; q2 = filter q2 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(q_FY2_1.selection,0,"Valu").asString()}}; result = group q1 by '{{column(static_1.selection, ["Valu"]).asObject()}}' full, q2 by '{{column(static_1.selection, ["Valu"]).asObject()}}'; result = foreach result generate coalesce(q1.'{{column(static_1.selection, ["Valu"]).asObject()}}', q2.'{{column(static_1.selection, ["Valu"]).asObject()}}' ) as '{{column(static_1.selection, ["Valu"]).asObject()}}', round(sum(q1.'Amt'),0) as 'Pipeline1 Amt',round(sum(q2.'Amt'), 0) as 'Pipeline2 Amt', round((sum(q1.'Amt')- sum(q2.'Amt')), 0) as 'Difference'; result = order result by ('{{column(static_1.selection, ["Valu"]).asObject()}}'asc); q3 = filter q by {{column(timeStampText_4.selection,["Snapshot1"]).asEquality('timeStampText')}}; q3 = filter q3 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,"Valu").asString()}}; q4 = filter q by {{column(timeStampText_5.selection,["Snapshot2"]).asEquality('timeStampText')}}; q4 = filter q4 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(q_FY2_1.selection,0,"Valu").asString()}}; |
Archives
June 2025
|