Date Filter problem

[Anonymous]

I have my ReservationTotal in a table that has 2 date fields: the ActivityDate and the BookDate. I need to give a way to choose year, month then day and for every step it should grab the totals and substract the total in activity from the total booked.

In order to simplify the selector I am using another datefield that is unrelated: InventoryDate.

Now this is the code I use to show the totals filtering by ActivityDate.  Year and month work perfectly. but the line where I try to do it for date doesnt work. WIth the code below, if the InventoryDate.C.Date is not selected it correctly shows the month total but as soon as I select an individual date it shows nothing.

SUM({< 

ActivityDate.C.Year = {$(=if(GetSelectedCount(InventoryDate.C.Year)>0, InventoryDate.C.Year,'*'))},

ActivityDate.C.Month = {$(=if(GetSelectedCount(InventoryDate.C.Month)>0, InventoryDate.C.Month,'*'))},

ActivityDate.C.Date = {$(=if(GetSelectedCount(InventoryDate.C.Date)>0,InventoryDate.C.Date,'*'))}

>}ReservationTotal)

Now If i Change it to: (watch how I move the single quotes to around all the formulas)

SUM({< 

ActivityDate.C.Year = {$(=if(GetSelectedCount(InventoryDate.C.Year)>0, InventoryDate.C.Year,'*'))},

ActivityDate.C.Month = {$(=if(GetSelectedCount(InventoryDate.C.Month)>0, InventoryDate.C.Month,'*'))},

ActivityDate.C.Date = {'$(=if(GetSelectedCount(InventoryDate.C.Date)>0,InventoryDate.C.Date,"*"))'}

>}ReservationTotal)

It correctly shows the number when I select the single date but it shows nothing when there is nothing selected. Apparently to show all it has to be a single quoted asterisk: '*'

I tried moving the single quotes to only be around the Date function but nothing works and it just happens with this final field.

[sunny_talwar · Accepted solution]

May be try this:

SUM({<

ActivityDate.C.Year = {$(=if(GetSelectedCount(InventoryDate.C.Year)>0, Chr(39) & Concat(DISTINCT InventoryDate.C.Year, Chr(39) & ',' & Chr(39)) & Chr(39),'*'))},

ActivityDate.C.Month = {$(=if(GetSelectedCount(InventoryDate.C.Month)>0, Chr(39) & Concat(DISTINCT InventoryDate.C.Month, Chr(39) & ',' & Chr(39)) & Chr(39),'*'))},

ActivityDate.C.Date = {$(=if(GetSelectedCount(InventoryDate.C.Date)>0, Chr(39) & Concat(DISTINCT InventoryDate.C.Date, Chr(39) & ',' & Chr(39)) & Chr(39),'*'))}

>}ReservationTotal)

If they have the same values (or if Inventory have all the values that are possible within Activity), then this can work also

SUM({<

ActivityDate.C.Year = p(InventoryDate.C.Year),

ActivityDate.C.Month = p(InventoryDate.C.Month),

ActivityDate.C.Date = p(InventoryDate.C.Date)

>}ReservationTotal)

[Anonymous]

Thank you so much, in order to have all the information here I want to add that the second solution you proposed did not work at all, however the first one worked like a charm. COuld I bother you with a short explanation of what concatenating the Chr(39) is doing?

[sunny_talwar]

Sure, when you select more than one year, InventoryDate.C.Year data have multiple values. Which one should it show? QlikView doesn't know that. Unless you add an aggregation function, on multiple selection of year, InventoryDate.C.Year is just null. Concat(DISTINCT InventoryDate.C.Year, ', ') will give you something like this 2015,2016 when you select 2015 and 2016 in your InventoryDate.C.Year field. For year this might still work without single quotes, but Date might not work without single quotes or double quotes around it in set analysis

Date = {'12/01/2016', '12/07/2016'}

To get those single quotes, we use Chr(39) which is equivalent of using a single quote.