topic Re: Help with Date Set analysis in QlikView

Help with Date Set analysis

hopkinsc — Mon, 20 Jan 2014 11:55:36 GMT

Hi, i need some help with some set analysis.

I have a field 'Reporting Year Index' which is currently '6' for this year.

I am trying to get some set analysis working which will sum YTD sales.

Sum({<Trans_Date={$(=max([Reporting Year Index))}>}Sales)

Can anyone see what i am doing wrong please?

Re: Help with Date Set analysis

tresB — Mon, 20 Jan 2014 12:05:50 GMT

Try putting " " like: Sum({<Trans_Date={"$(=max([Reporting Year Index))"}>}Sales)

If the date format matches, this should work.

Re: Help with Date Set analysis

jonathandienst — Mon, 20 Jan 2014 12:12:00 GMT

This will work if Trans_Date is equal to 6, but I suspect it is a date value.You are missing the closing ] for the reporting year index.

Assuming Trans_Date is a date. you will need to convert the reporting year index into a date, but you have not provided enough information. Again assuming that there is a base date for the index, you could try

{<Trans_Date = {">=$(=YearStart(vBaseYear, [Reporting Year Index])) <=$(=YearEnd(vBaseYear, [Reporting Year Index]))"}>}

HTH

Jonathan

Re: Help with Date Set analysis

hopkinsc — Mon, 20 Jan 2014 12:12:57 GMT

Hi, thanks.

That doesnt work as the [Reporting Year Index] field is an index number not a date.

so i want the Trans_Date field to use all dates that are linked to the max reporting year index field if that makes sense.

Reporting Year Index Reporting Date

5 01/01/2013

5 15/05/2013

6 27/12/2013

6 28/12/2013

6 01/01/2014

6 17/01/2014

So in the above example the max reporting date index is 6

so i would want Trans_Date to include any date with a Reporting Year Index of 6.

Thanks

Re: Help with Date Set analysis

jonathandienst — Mon, 20 Jan 2014 12:24:22 GMT

With the additional information:

{<Trans_Date = {">=$(=YearStart('1/12009', max([Reporting Year Index]))) <=$(=YearEnd('1/12009', max([Reporting Year Index])))"}>}

Edit - forgot the max()

Re: Help with Date Set analysis

Mon, 20 Jan 2014 12:28:44 GMT

Change your expression as follows:

Sum({<Trans_Date={$(#=max([Reporting Year Index))}>}Sales)

Re: Help with Date Set analysis

tresB — Mon, 20 Jan 2014 12:29:34 GMT

Try using p() like:

=Sum({<[Reporting Date]=p({<[Reporting Year Index]={"$(=Max([Reporting Year Index]))"}>})>}Sales)

PFA

Re: Help with Date Set analysis

hopkinsc — Mon, 20 Jan 2014 12:37:09 GMT

Hi Janathan,

Thanks for your help. I can't get that to work.

This is my actual expression...

sum(

{<Status_Name={'Rejected','Job Completed','Not Completed'}

,[2ndStatus]={'Defect Not As Described'}

,Status_Date={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}

,CommentDate={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}>}

Re: Re: Help with Date Set analysis

hopkinsc — Mon, 20 Jan 2014 12:50:47 GMT

Thanks, that also doesnt work.

I have attached a sample..

Re: Help with Date Set analysis

Mon, 20 Jan 2014 13:08:49 GMT

I think the basic sub-expression are wrong in your expression.

=sum(

{<Status_Date={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}

,[Comment Date]={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}>}

[Job Count])

1) You are using [Reporting Date Index] in exp, but in the sample file we have [Reporting Year Index].

2) The date passed to YearStart function is '1/12009', which is not a valid date.

Can you please break your expression and then embed into one?

Thanks,

Angad

Re: Re: Help with Date Set analysis

hopkinsc — Mon, 20 Jan 2014 13:41:22 GMT

Hi All,

I still can't get this to work..

Re: Re: Re: Help with Date Set analysis

Anonymous — Mon, 20 Jan 2014 20:03:34 GMT

I've fixed a few errors in your expression:

=sum(

{<Status_Date={">=$(=YearStart('1/1/2009',max([Reporting Year Index])))<=$(=YearEnd('1/1/2009',max([Reporting Year Index])))"}

,[Comment Date]={">=$(=YearStart('1/1/2009',max([Reporting Year Index])))<=$(=YearEnd('1/1/2009',max([Reporting Year Index])))"}>}

[Job Count])

The errors were:

1. As pointed earlier, 1/12009 is no a date. Replaced with 1/1/2009

2. There is no field "Reported Date Index" in your app. There is "Reported Year Index".

3. Added max to specify the index.

Now, if you don't select index, the max is 6, and the year is 2015. The expression result is 0. You have to select earlier index to get the non-0 result. Or, use 2008 instead of 2009 in the expression, as in the attached example.