topic Re: Date Formatting In Set Analysis in QlikView

Date Formatting In Set Analysis

Anonymous — Wed, 24 Jul 2013 22:11:06 GMT

I'm trying to implement an expression with set analysis:

=(NumFiled+1)/(only({<Cal.Date={"$(=Date(Min(Cal.Date),'DD-MMM-YY'))"}>}NumField)+1)-1

Can't figure out where I'm doing wrong.

Re: Date Formatting In Set Analysis

Miguel_Angel_Baeyens — Wed, 24 Jul 2013 22:21:17 GMT

Hi,

If you do a Date() on Cal.Date, do you get a numeric value? If so, make sure that the Only() function returns actually only one value (use it in a text object, for example, and use Min({1} Cal.Date) instead). Finally, the result of the Date() function must be in the same format that the Cal.Date format (e.g.: if it's a complete timestamp with hours, minutes and seconds, you will need to add them).

Hope that helps.

Miguel

Re: Date Formatting In Set Analysis

Thu, 25 Jul 2013 05:39:49 GMT

try this

=(NumFiled+1) / (sum({<Cal.Date={"$(=Date(Min(Date(Cal.Date,'DD-MMM-YY')))"}>}NumField)+1)-1

Re: Date Formatting In Set Analysis

Anonymous — Thu, 25 Jul 2013 13:35:47 GMT

Thank you both. Actually, my date formatting was not matching Cal.Date format ('DDMMMYY' and not 'DD-MMM-YY'), also used {1} and "sum" instead of "only". So, the expression that worked is:

=(NumField+1)/(sum({<Cal.Date={"$(=Date(Min({1} Cal.Date), 'DDMMMYY'))"}>}NumField)+1)-1

Thank you again.