Select different date ranges depending within dimension

[#1]

Hi,

I have a table with date, id and traffic. I need to create a filter that will allow me to select a specific id and date range as well as another id with a different date range. For instance, in the table below I need to select ID 207 where date's > 7/16 and ID 69 where date's > 7/19:

I need this selection as a filter and have been trying to do so in a multibox with the following expression, however, it returns a selection from 7/16 forward and does not return correct dates for id 69:

=if(Match(ID, 69)>0 AND Date > '7/19/2014' OR Match(ID, 207)>0 AND Date > '7/16/2014' , 'Test2')

I've attached a sample app. If anyone can't point me in the right direction it would be appreciated.

Best,

Matt

[aveeeeeee7en]

Hi Matthew

Try this Expression:

if(ID='207',sum({<Date={"=Date>'7/16/2014'"} >}Traffic),

if(ID='69',sum({<Date={"=Date>'7/19/2014'"} >}Traffic)))

Also, see the Attachment.

Hope that works.

Regards

Aviral Nag

[#3]

Thanks Aviral,

I actually need it as a filter that I can select. There will also be other fields that need to filter on the selection, so I don't want to sum traffic in the expression, but rather select specific IDs and date ranges.

Can you think of a way to incorporate this into a filter, as I was trying to do with the if/match in the mutlibox?

Thanks,

Matt

[#4]

Hi,

I think u can make use of variables here to store the date values and further make use of them in expressions.

[aveeeeeee7en · Accepted solution]

See this.

[MarcoWedel]

Hi Matt,

the reason for this behaviour might be that a selection in your multi box actually selects ANY corresponding values in both the Date and ID field. Any combination of these selected Dates and IDs therefore will be present in your straight table.

A solution could be to create a combined field that contains both the Date and ID values. The multi box expression then could be changed to select only in this combined field.

One implementation:

your mutli box selection:

modification:

using this expression:

=if(SubField(Date_ID, '@', 2)=69 AND Date#(SubField(Date_ID, '@', 1),'MM/DD/YYYY') > Date#('7/19/2014','MM/DD/YYYY') OR

    SubField(Date_ID, '@', 2)=207 AND Date#(SubField(Date_ID, '@', 1),'MM/DD/YYYY') > Date#('7/16/2014','MM/DD/YYYY')

    ,'Test2')

creation of the combined selector field in the script:

LOAD *,

     Date&'@'&ID as Date_ID

INLINE [

   Date, Country, ID, Traffic, Signups

    7/15/2014, ZM, 207, 1, 0

    7/15/2014, YT, 69, 1, 0

    7/15/2014, VN, 34, 1, 1

    .

    .

    .

hope this helps

regards

Marco

[#7]

Thanks a lot for the help Marco. This looks promising but for now I've implemented an approach similar to Nag's.