date question re {$<CallInDay = VisitDay , VisitDay = ,

[robert99]

I have a number of dates that at times I need to select by

Example VisitDate = date the work is done

CallIndate = date the call requewst is made etc

So I want to set up three  date select fields selection boxes (day/Mth and year) and use these boxes to select the appropraite period whether reporting by callin date or visitdate etc

I have used the most common reporting date (VisitDate) and changed for say charts needing callin date as follows:

Count (
{$<CallInDay =  VisitDay  ,  VisitDay = ,
CallInMth =VisitMth, VisitMth = ,
CallInYr = VisitYr,VisitYr = >}

DISTINCT Call_Site_Num & Call_LoggedDate)

This works but only if the appropriate day month and year are all selected.

If say only mth and year is selected nothing is returned. (until days 1 to 31 are highlighted if month comparisons are needed)

Is there a way to overcome this issue. So that if no days are selected but month = Feb is selected it would treat it as all days are selected for Feb

[swuehl · Accepted solution]

RJ,

look into the p() function for use in a set expression. Maybe like

Count (
{$<CallInDay =  p(VisitDay)  ,  VisitDay = ,
CallInMth = p(VisitMth), VisitMth = ,
CallInYr = p(VisitYr),VisitYr = >}

DISTINCT Call_Site_Num & Call_LoggedDate)

There are potentially also some different approaches to solve your problem, for example using a master calendar and a link table. There were recently some threads about this here in the forum and a design blog post by Henric.

Hope this helps,

Stefan

[robert99]

Thanks Stefan

A perfect solution that is exactly (nice and simple) what I was looking for

I've spend many hours on this and knew there must be a solution. But would have never though of trying P

I will also look for Henric's previous blog post (his mapping tables blog post has completely changed the way I set QV script up)

[swuehl]

Here are some references:

http://community.qlik.com/message/273800#273800

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

Have fun,

Stefan

[robert99]

Thanks

Interesting threads that has cleared up confusion I had re master calender (I understand hopefully what it is now) and link tables (likewise).

I agree with Henric (and mph12) though. I can see no situation at this point (but I'm still learning) where I would follow Rob's link table approach. But I always like to keep it as simple as possible otherwise I can confuse myself (let alone others that I pass work onto after completion).

Also I tested this and was surprised it worked. 

Load Date, Month(Date) as Month … ;

Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;

Load Min(Date)-1 as MinDate, Max(Date) as MaxDate resident TransactionTable   ;

For two reason:

Min and max does not have a group. But this works without group as long as I do not load Date in the third load. If I do then group is needed for this to work.

No source is given in the first two loads. (But the reason is given in the manual at the end of load).

This opens up a number of options for me that I will test at work next week.

[swuehl]

I agree with Henric (and mph12) though. I can see no situation at this point (but I'm still learning) where I would follow Rob's link table approach.

I think Rob mentioned one setting when this approach might be helpful: If you want to group your data by a common time line.

But for sure these approaches are not XOR, but OR.

Also I tested this and was surprised it worked. 

Load Date, Month(Date) as Month … ;

Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;

Load Min(Date)-1 as MinDate, Max(Date) as MaxDate resident TransactionTable   ;

For two reason:

Min and max does not have a group. But this works without group as long as I do not load Date in the third load. If I do then group is needed for this to work.

No source is given in the first two loads. (But the reason is given in the manual at the end of load).

Right, the HELP says about aggregation functions in the chart:

"These functions can only be used in field lists for Load statements with a group by clause."

This seems not to be correct, if you calculate the aggregation in gobal context (no grouping required) of the given source. In your above sample, you are telling QV to return the total minimum / maximum of field Date in table TransactionTable. As soon as you add another field in this load, a group by using this field would be needed, of course.

Second, QV allows the construction of something like pipes using preceding loads.

You need to read these loads from the bottom to the top. So first the LOAD with the aggregation functions will be executed using the source resident table TransactionTable. Then, the resulting records from this will be inputed into the second LOAD with the while loop. Last, the resulting records from this into the LOAD that creates the other calendar fields.

Hope this helps,

Stefan