topic Re: Select different date ranges depending within dimension in QlikView

Select different date ranges depending within dimension

Sun, 03 Aug 2014 16:25:07 GMT

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

Re: Select different date ranges depending within dimension

aveeeeeee7en — Sun, 03 Aug 2014 16:47:16 GMT

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

Re: Select different date ranges depending within dimension

Sun, 03 Aug 2014 17:22:30 GMT

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

Re: Select different date ranges depending within dimension

Sun, 03 Aug 2014 17:25:42 GMT

Hi,

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

Re: Re: Select different date ranges depending within dimension

aveeeeeee7en — Sun, 03 Aug 2014 19:42:10 GMT

See this.

Re: Select different date ranges depending within dimension

MarcoWedel — Mon, 04 Aug 2014 00:45:06 GMT

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

Re: Select different date ranges depending within dimension

Mon, 04 Aug 2014 17:57:06 GMT

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