filter based on period window and end date?

[#1]

Hi,

I have a scenario for filter and I will appreciate if someone could help me here. I have a requirement where I have two filters. One is time period window, for sample 14 days, 28 days, 45 days etc and the second filter is the end date. When I select 14 days window from list box and then end date from calendar, I want the data to show till 14 days back from the selected date.

For example, If the selected end date is 30th September and the time period window is selected to be 14 days, then The dashboard should show data from 17th September till 30th September

I will really appreciate your help here

Regards

Arif

[Nicole-Smith]

See the attached for an example on how to do so.  It is done by using set analysis on the expression in the table.

[#3]

Hi Nicole,

Many thanks for your urgent help. Can you please tell me how do I find the attachment. I am not able to see your attachment in this discussion

Syed

[Nicole-Smith]

On my post above, just click on the link (name of the .qvw):

[#5]

Hi Nicole,

Many thanks. I was able to use your expression and it helped me. Now I have a minor problem with this expression. When I try to drill down from one category to another, it does not work with this expression. when I use a simple count, the drilldown works but when I use your expression, the drilldown does not work for me. Infact the chart shows the data at lowest level and does not go up in the category.

When I try to separately see the chart at the up cateogry and low category, the chart shows the data fine

Arif

[Nicole-Smith]

In order for me to help you, you need to post a .qvw:

Preparing examples for Upload - Reduction and Data Scrambling

[#7]

Hi Nicole,

I was able to fix the issue. It was more related to data rather than the expression. I have a second question now.

If I want to have another expression in the same chart that shows the same period data for previous year, how can I change this expression

For example, If the selected end date is 30th September 2013 and the time period window is selected to be 14 days, then Then the below expression shows data from 17th September till 30th September (2013)

Count({<OffenseDateConv={'>=$(=date(GetFieldSelections(OffenseDateConv)-subfield(TimePeriod, ' ', 1)))<=$(=GetFieldSelections(OffenseDateConv))'}>}Casenum)

Now if I want to add another expression for comparison that shows the same kind of data for previous year than the year selected, then how can I change the above expression. I tried the below expression but it did not work

Count({<OffenseDateConv={'>=$(=date(AddYears(GetFieldSelections(OffenseDateConv), -1)-subfield(TimePeriod, ' ', 1)))<=$(=AddYears(GetFieldSelections(OffenseDateConv), -1)'}>}Casenum)

Arif

[Nicole-Smith]

Count({<OffenseDateConv={'>=$(=date(addmonths(GetFieldSelections(OffenseDateConv)-subfield(TimePeriod, ' ', 1),-12)))<=$(=date(addmonths(GetFieldSelections(OffenseDateConv),-12)))'}>}Casenum)