Rolling 6 months data as per the time frame selection with multiple scenario or single time frame time filter or default view

[manoranjan_321988]

Hi Team,

Attached my QVF file and attached my sample data , the requirement is when user select any time filter like year, month-year, date, week they need to see the rolling 6 months data as a bar chart  and dim in the bar chart  has to change accordingly

default view in the bar chart is month-year as dim and 6 month-year rolling data and if user select  filter then accordingly it have to change as per the below examples

Example:

1)if they select multiple(combination) time frame filter example year and month-year then it has to show selected month-year with rolling 6 months data and dim has to change accordingly as month-year bcoz lowest granularity is monthyear data. 

like  multiple combination filter selection happens and  as per the lowest granularity dim has to come in to bar chart and show the  6 rolling bar in the chart. 

2)if they select only year filter or any time frame as single filter selection  then it shd show the dim as per the filter  in bar chart  and rolling  6 data in the bar chart.

like if they select year filter only then bar chart  dim is year with rolling 6 yrs data

like if they select date filter only then bar chart  dim is date with rolling 6 dates data

like if they select week filter only then bar chart  dim is week with rolling 6 weeks data

like if they select month- year filter only  then bar chart  dim is month- year filter with rolling 6 month- year filterdata

i almost achieved 80% in the attached but where i am getting failed  in the default view its not showing the rolling 6 months data and if we select the date also its getting failed its showing other dates with zero value and set analysis and dim also not restricting the 6 bar data

the chart shd not suppressed with zero value bcoz they want to see the zero values in the 6 bar chart. 

avoid the show hide condition of the measure because in future in bar chart  they ll request for 2nd dim to add it , if we add multiple measure(show/hide) then we can add 2nd dim in the bar chart.

you can play around on my qvf and you can understand the issue

[manoranjan_321988]

@olofmalmstrom could you please help me on this since the calculated dim as default view  also not working in this poc  ,long back you shared in this link

 https://community.qlik.com/t5/App-Development/Bar-chart-to-show-only-12-months-rolling/m-p/2495151#M... 

[manoranjan_321988]

team any response for my issue on the default view as well the date filter selection

[marcus_sommer]

Personally I would use at least two variables which query the current selection-state and picking from it the information which field should be used as dimension and which field for the set analysis condition. Appropriate set default-values in the queries might be also used as display-conditions against dimensions/expressions or the objects.

Such variable-query may look like:

= pick(
    match(
     rangemax(
      sign(getselectedcount(Date)) * 4,
      sign(getselectedcount(Week)) * 3,
      sign(getselectedcount(Month)) * 2, 1),
    1, 2, 3, 4),
  'DefaultDim', 'Month', 'Week', 'Date')

Similar results could be created by using nested if-loops against getselectedcount() or getfieldselections() by considering the wanted order of most granular values or reverse and/or with any other logic/exceptions.

After that you could use this variable as dimension like:

[$(DimVar)]

For the set analysis it's quite the same and the expression may look like:

sum({< [$(SetVar)] = {">$(=max([$(SetVar)])-6)<=$(max([$(SetVar)]))"}>} Value)

The two different variables are necessary to ensure that the set analysis fields are running numbers to consider things like a year-switch because Month = 1 -6 wouldn't return 8 from the previous year else just - 5. Therefore using running numbers which could be easily created within a sorted calendar per autonumber() or maybe also with interrecord-functions like peek() and previous(). 

[olofmalmstrom]

Not sure i got the question correct, but try with this:

Dimension:

=IF(GetSelectedCount(Date)>0,Date,
 IF(GetSelectedCount(Week)>0,Week,
 IF(GetSelectedCount(Month)>0,Month,
 IF(GetSelectedCount(MonthYear)>0,MonthYear,
 IF(GetSelectedCount(Year)>0,Year,
 
 IF(MonthYear>=date(MonthStart(AddMonths(Today(), -12)),'YYYY-MM') and MonthYear <=date(MonthEnd(AddMonths(Today(), -1)),'YYYY-MM'), MonthYear)
 
  )))))

Measure:

=IF(GetSelectedCount(Date)>0,sum({<Year=,MonthYear=,Month=,Week=,Date=,Date={">=$(=date(max(Date)-5))<=$(=Max(Date))"}>}METRIC_VALUE),
 IF(GetSelectedCount(Week)>0,sum({<Year=,MonthYear=,Month=,Week=,Date= {">=$(=date(max(Date)-35)) <=$(=Max(Date))"}>}METRIC_VALUE),
 IF(GetSelectedCount(Month)>0,sum({<Year=,MonthYear=,Month=,MonthYear= {">=$(=date(addmonths(max(MonthYear),-5),'MMM-YYYY')) <=$(=Max(MonthYear))"}>}METRIC_VALUE),
 IF(GetSelectedCount(MonthYear)>0,sum({<Year=,MonthYear=,Month=,MonthYear= {">=$(=date(addmonths(max(MonthYear),-5),'MMM-YYYY')) <=$(=Max(MonthYear))"}>}METRIC_VALUE),
 IF(GetSelectedCount(Year)>0,sum({<Year=,Year= {">=$(=max(Year)-5) <=$(=Max(Year))"}>}METRIC_VALUE),
 
 sum({<MonthYear= {">=$(=date(addmonths(max(MonthYear),-5),'MMM-YYYY')) <=$(=Max(MonthYear))"}>}METRIC_VALUE))))))

/Olof

[manoranjan_321988]

i tried ur logic in the file but not working attached is the qvf file and also the calculated default dim has to be max month -5 , and below is the screenshot and qvf file right side ur expression and bottom table chart i tried only the monthyear exp it says invalid dim

[manoranjan_321988]

@marcus_sommer  could you pls share ur qvf file

[olofmalmstrom]

Here is my qvf if you want to check it. 

[olofmalmstrom]

@manoranjan_321988 New approach, try this one 🙂

[manoranjan_321988]

I saw  your qvf file you have disabled the include null value but dec2023 have the value that is going in to null value 

scenario 1

i noted when i select the date its not showing 6 bar data it show other data

scenario 2

and also one doubt in the calculated dim(default dim) we need to pass with max (month year) and need to avoid the today() and rolling shd be 5 months and you have mentioned 12 months i cant understand this one,but in the measure u have passed max(month year) and -5 as default ,measure