topic Re: Rolling 6 months data as per the time frame selection with multiple scenario or single time frame time filter or default view in App Development

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

manoranjan_321988 — Thu, 09 Jan 2025 10:50:28 GMT

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

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

manoranjan_321988 — Thu, 09 Jan 2025 12:16:34 GMT

@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#M102491

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

manoranjan_321988 — Thu, 09 Jan 2025 13:31:43 GMT

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

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

marcus_sommer — Thu, 09 Jan 2025 13:38:39 GMT

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().

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

olofmalmstrom — Thu, 09 Jan 2025 13:51:49 GMT

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

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

manoranjan_321988 — Thu, 09 Jan 2025 15:38:50 GMT

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

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

manoranjan_321988 — Thu, 09 Jan 2025 15:41:06 GMT

@marcus_sommer could you pls share ur qvf file

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

olofmalmstrom — Thu, 09 Jan 2025 15:50:14 GMT

Here is my qvf if you want to check it.

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

olofmalmstrom — Thu, 09 Jan 2025 17:44:49 GMT

@manoranjan_321988 New approach, try this one 🙂

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

manoranjan_321988 — Thu, 09 Jan 2025 18:02:04 GMT

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

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

manoranjan_321988 — Thu, 09 Jan 2025 21:32:04 GMT

results are meeting with your file, I cant understand the format of date you using as D/B/YYYY may i know the logic and also in the week your performing before the $ of the weekend start function. so need some inputs from you on this

And also i tried another way of aggr as dim below and ,measure as below but when selecting the week number in the filter it getting fail can you help me on this also by achieving thru aggr

dim

=IF(GetSelectedCount(Date)>0,
aggr(only({<Date={">=$(=date(max(Date)-5))<=$(=Max(Date))"}>}Date),Date)
,
IF(GetSelectedCount(Week)>0 or GetSelectedCount(weekstartdate),
weekstartdate,

IF(GetSelectedCount(Month)>0,Month,
IF(GetSelectedCount(MonthYear)>0,MonthYear,
IF(GetSelectedCount(Year)>0,Year,

aggr(only({<MonthYear={">=$(=date(addmonths(max(MonthYear),-5),'MMM-YYYY'))<=$(=Max(MonthYear))"}>}MonthYear),MonthYear)

)))))

exp

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

olofmalmstrom — Thu, 09 Jan 2025 22:43:51 GMT

Date format is a typo, should be M/D/YYYY

The question of week, not sure I understand the question, what part is it that is unclear?

The aggr question, you have the same expression for Dim and Exp, guess copy paste error. But if my suggestion works, if I understand you correct, why try something else?

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

olofmalmstrom — Thu, 09 Jan 2025 22:57:02 GMT

Another thing, I forgot the Year expression

=IF(GetSelectedCount(Date)>0, IF(Date>=date($(=max(Date)-5),'M/D/YYYY') AND Date<=date($(=max(num(Date))),'M/D/YYYY'),Date),
IF(GetSelectedCount(Week)>0,IF(Date>=weekstart(date($(=max(Date)-35),'M/D/YYYY')) AND Date<=weekend(date($(=max(num(Date))),'M/D/YYYY')),Week),
IF(GetSelectedCount(Month)>0,IF(MonthYear>=date(MonthStart(AddMonths(date($(=max(num(Date))),'M/D/YYYY'), -5)),'YYYY-MM') and MonthYear <=date(MonthEnd(AddMonths(date($(=max(num(Date))),'M/D/YYYY'), 0)),'YYYY-MM'), Month),
IF(GetSelectedCount(MonthYear)>0,IF(MonthYear>=date(MonthStart(AddMonths(date($(=max(num(Date))),'M/D/YYYY'), -5)),'YYYY-MM') and MonthYear <=date(MonthEnd(AddMonths(date($(=max(num(Date))),'M/D/YYYY'), 0)),'YYYY-MM'), MonthYear),
IF(GetSelectedCount(Year)>0,IF(Year>=Year(date(YearStart(AddYears(date($(=max(num(Date))),'M/D/YYYY'), -5)),'YYYY')) and Year <=Year(date(YearEnd(AddYears(date($(=max(num(Date))),'M/D/YYYY'), 0)),'YYYY')), Year),

IF(MonthYear>=date(MonthStart(AddMonths(vEndDate, -5)),'YYYY-MM') and MonthYear <=date(MonthEnd(AddMonths(vEndDate, 0)),'YYYY-MM'), MonthYear)

)))))

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

manoranjan_321988 — Fri, 10 Jan 2025 04:16:49 GMT

Hi olo,

I am also using the aggr and what happening , when i select the week number in my aggr its showing extra x-axis but in ur cal dim its working fine , could you pls help me on this aggr to get correct it dim, your if condition is working fine but seeking the same result in the week also with aggr. attached qvf and screen shot for ur reference

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

Bhushan_Mahajan — Fri, 10 Jan 2025 05:58:55 GMT

@manoranjan_321988 Use below expression

Sum({1<Date={">=$(=Date(AddMonths(max(Date),-6),'MM/DD/YYYY'))<=$(=Date(Max(Date),'MM/DD/YYYY'))"}>} METRIC_VALUE)

Also for year selection you can write geteselection condition but your data not contain values for last 6 years.

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

marcus_sommer — Fri, 10 Jan 2025 08:02:13 GMT

Everything is much too complicated as needed for such rather simple task!

No calculated dimension is necessary and especially no aggr() for it - just providing the dimension per variable. Further, don't use strings or any kind of formatted values for calculations respectively matching - else only pure numbers. It's only error-prone and requires extra efforts to resolve the values in a way that they could be processed.

Beside this it's not very efficient to develop such logic within a single step. Better is to divide the task in n granular steps which are after proving to be working as expected further extended and combined. In your case you may start with something like:

Var: vDim: = if(getselectedcount(Week), 'Week', 'Month')
Dim: $(vDim)
Exp: sum(Value)

which just checked if the used dimension could be dynamically changed by selecting any week-value. If it's not working means that anywhere is any mistake in the syntax or a typo in the fields or similar. If it's working the next field-selection may be included and after that the next and then you may focus to ensure a certain order of them and then the next may to implement any exception-handling (maybe no selections at all or no available values for the selections or ...).

With a similar approach could be the set condition developed. Like mentioned above those fields needs to be numeric and ideally without any formatting and being continuously to simplify the logic by any kind of a period-switch. It's quite simple to create all these fields within the calendar.

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

manoranjan_321988 — Fri, 10 Jan 2025 08:27:58 GMT

could you pls help me to share your qvf file on this sample data of your findings and workout for this requirement

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

marcus_sommer — Fri, 10 Jan 2025 08:46:54 GMT

I have no qvf and also no Sense available. You don't need an already prepared application. The above hinted step by step approach is quite simple and within a few minutes you should get the first intermediate results. Further only by doing it yourself you will learn the how's and why's which is much more important as getting a fast solution.

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

manoranjan_321988 — Mon, 13 Jan 2025 04:42:06 GMT

Tried your suggestion with that only initially but the thing is we need to enable the suppress zero value in my scenario we shd not suppress the zero value and then olo provided the calculated condition, its working fine as of now

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

manoranjan_321988 — Mon, 13 Jan 2025 04:44:30 GMT

Hi olo,

Accepting your suggestion as solution , if any issues or concern will open new link and tag you in that,

Much thanks for your kind support and help on this.

And my aggr is failing when i select some other filter like pdt field, so going with you if cond with calculated and set analysis exp

Regards

Mano