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]

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

=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)

)))))

[olofmalmstrom]

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?

[olofmalmstrom · Accepted solution]

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)

)))))

[manoranjan_321988]

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 

[Bhushan_Mahajan]

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

[marcus_sommer]

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.

[manoranjan_321988]

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

[marcus_sommer]

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.

[manoranjan_321988]

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 

[manoranjan_321988]

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