topic Re: Dynamic 12-Months period window on a Bar Chart in App Development

Dynamic 12-Months period window on a Bar Chart

Keitaru — Tue, 04 Jun 2024 04:42:13 GMT

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.

Re: Dynamic 12-Months period window on a Bar Chart

TauseefKhan — Tue, 04 Jun 2024 10:10:51 GMT

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.********

Re: Dynamic 12-Months period window on a Bar Chart

Keitaru — Wed, 05 Jun 2024 04:44:41 GMT

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.