- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dynamic 12-Months period window on a Bar Chart
Hi Qlik experts,
Have a question regarding moving 12 month windows.
Have chart below that displays rolling 12 month window.
How do I have the window of the month move based on filter selection also when nothing is selected the chart should show max rolling 12 month to Date, my suspicion is that I need to use of getfieldselection()
Chart Code (Original)
=Date(aggr(only({< Date ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'DD/MM/YYYY') & '<=' & Date(Max(AsOfYearMonth), 'DD/MM/YYYY'))"}>}MonthName),MonthName),'MMM YY')
Updated:Made a variable calling it vMonthWindow. =if( len(GetFieldSelections(MnthYr)) <= 0, Max(order_contract_tradedate),Date(MnthYr,'DD/MM/YYYY'))Used vMaxMonth instead:
=Date(Max(date),'DD/MM/YYYY')And Added it to my Dimension:=Date(aggr(only({< Date ={"$(='>=' & Date(AddMonths(vMovingMaxMonth,-11),'DD/MM/YYYY') & '<=' & Date(AddMonths(vMovingMaxMonth,0),'DD/MM/YYYY'))"}>}MonthName),MonthName),'MMM YY')
Added to Measure instead of Dimension:
=(Count( {<Date={'Settled'},Date ={"$(='>=' & Date(AddMonths(vMaxMonth,-11),'DD/MM/YYYY') & '<=' & Date(AddMonths(vMaxMonth,0),'DD/MM/YYYY'))"}>} ordercontractid) / 100)
+ sum( {1<Date ={"$(='>=' & Date(AddMonths(vMaxMonth,-11),'DD/MM/YYYY') & '<=' & Date(AddMonths(vMaxMonth,0),'DD/MM/YYYY'))"}>} 0)
The Sum({1<...>} 0 ) is a work around in barchart to show months containing null data on the bar chart
when testing out Date(AddMonths(vMaxMonth,-11),'DD/MM/YYYY') and Date(AddMonths(vMaxMonth,0),'DD/MM/YYYY') individually they seem to be working showing Changes in the dates when certain Filter selection had been done. But when I added it to the chart dimension it d
No MnthYr Period selected:
random Month Selected (Previous):
After Implementing the above to the measure instead of the Dimension i got this instead after selecting a filter month.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Keitaru,
Dynamic Rolling 12-Month Window:
Variable:
vMaxMonth
=if(len(GetFieldSelections(MonthYr)) <= 0, Max(order_contract_tradedate), Date(MonthYr, 'DD/MM/YYYY'))
This variable checks if any month-year filter is selected. If not, it uses the maximum date from the entire dataset.
Dimension: To calculates the rolling 12-month window based on the vMaxMonth variable.
=Date(aggr(only({< Date ={"$(='>=' & Date(AddMonths(vMaxMonth, -11), 'DD/MM/YYYY') & '<=' & Date(vMaxMonth, 'DD/MM/YYYY'))"}>} MonthName), MonthName), 'MMM YY')
Measure:
=(Count({<Date={'Settled'}, Date ={"$(='>=' & Date(AddMonths(vMaxMonth, -11), 'DD/MM/YYYY') & '<=' & Date(vMaxMonth, 'DD/MM/YYYY'))"}>} ordercontractid) / 100)
+ sum({1<Date ={"$(='>=' & Date(AddMonths(vMaxMonth, -11), 'DD/MM/YYYY') & '<=' & Date(vMaxMonth, 'DD/MM/YYYY'))"}>} 0)
******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Keitaru,
Dynamic Rolling 12-Month Window:
Variable:
vMaxMonth
=if(len(GetFieldSelections(MonthYr)) <= 0, Max(order_contract_tradedate), Date(MonthYr, 'DD/MM/YYYY'))
This variable checks if any month-year filter is selected. If not, it uses the maximum date from the entire dataset.
Dimension: To calculates the rolling 12-month window based on the vMaxMonth variable.
=Date(aggr(only({< Date ={"$(='>=' & Date(AddMonths(vMaxMonth, -11), 'DD/MM/YYYY') & '<=' & Date(vMaxMonth, 'DD/MM/YYYY'))"}>} MonthName), MonthName), 'MMM YY')
Measure:
=(Count({<Date={'Settled'}, Date ={"$(='>=' & Date(AddMonths(vMaxMonth, -11), 'DD/MM/YYYY') & '<=' & Date(vMaxMonth, 'DD/MM/YYYY'))"}>} ordercontractid) / 100)
+ sum({1<Date ={"$(='>=' & Date(AddMonths(vMaxMonth, -11), 'DD/MM/YYYY') & '<=' & Date(vMaxMonth, 'DD/MM/YYYY'))"}>} 0)
******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Let me try your solution as I came out with my own after trial and error below:
Variable
=Date(max(order_contract_tradedate),'DD/MM/YYYY')
Measure
Count( {1<order_contract_orderstatus={'Settled'}, Date=, Year=, Month=, Date ={">=$(=Date(AddMonths(vMaxMonth,-11),'DD/MM/YYYY')) <=$(=Date(AddMonths(vMaxMonth,0),'DD/MM/YYYY'))"}>} ordercontractid)
+ sum({1<Date=, Year=, Month=, Date ={">=$(=Date(AddMonths(vMaxMonth,-11),'DD/MM/YYYY')) <=$(=Date(AddMonths(vMaxMonth,0),'DD/MM/YYYY'))"}>} 0)
Just that have another issue to due with no data associated to the date that available on the master calendar.