topic Re: Min Date for specific customer field in QlikView

Min Date for specific customer field

Anonymous — Tue, 02 Feb 2016 20:04:00 GMT

I'm trying to create a variable that calculates the first job creation date and job posting date (%Date) for a specific customer.

Below picture shows the equation working on a straight table. but when i create the variable vFirstOrderDate = min(%Date) I get the first date in my calendar.

Is there a simple solution?

Re: Min Date for specific customer field

sunny_talwar — Tue, 02 Feb 2016 21:13:59 GMT

Not sure what the concern is? Can you elaborate a little more?

Re: Min Date for specific customer field

Anonymous — Tue, 02 Feb 2016 21:42:56 GMT

I need to create a variable/equation that can pull the first date a customer had a posting. I will use that to count how many first time customer postings there are per week/year/month.

Re: Min Date for specific customer field

petter — Tue, 02 Feb 2016 22:33:30 GMT

It is probably a matter of when the variable is being evaluated. If you define the variable vFirstOrderDate with no equal sign it will be defined as a string that later can be evaluated with a $-sign expansion in the straight table. If it has an equal sign it will not look like a string but as an expression and it will be evaluated immediately. That means that it will not have any dimensions that qualify it and will pick the first date in your calendar.

In the load script the variable can be created like this:

LET vFirstOrderDate = 'Min(%Date)' ;

And be included as an expression like this:

$(vFirstOrderDate)

Re: Min Date for specific customer field

Anonymous — Tue, 02 Feb 2016 22:57:18 GMT

This works great for the straight table. but I want to be able to count how many orders were created in a given week/month/year whatever my selection may be. I get the error "nested aggregation not allowed" when i attempt to count the $(vFirstOrderDate).