topic SV:Re: Set analysis with less than or equal in QlikView

Set analysis with less than or equal

Thu, 08 Jul 2010 14:36:54 GMT

I've been struggling with this set analysis.

Sum(

Month={$(=Month(Today()))},

Year={$(=max(Year)-1)},

InvoiceDate={"<=2009-07-04"}

}

if(CurrNo=1,Invoice_NetPriceEUR,if(CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE))

)

It does give me what I want but now I wants to replace the last row with: AddMonths(Date(Today()),-12).

Shouldn't this work?

InvoiceDate={$(<=AddMonths(Date(Today()),-12))}

I also tried a few variants but no luck. My best shot was:

InvoiceDate={"<="{$(=AddMonths(Date(Today()),-12)}

Thanks for any help!

Set analysis with less than or equal

Thu, 08 Jul 2010 15:07:17 GMT

InvoiceDate={"<=$(=AddMonths(Date(Today()),-12)"}

SV:Re: Set analysis with less than or equal

Thu, 08 Jul 2010 15:15:56 GMT

I'm sorry but that made no difference. Is the syntax correct?

SV:Re: Set analysis with less than or equal

Miguel_Angel_Baeyens — Thu, 08 Jul 2010 16:27:34 GMT

Hello Hakan,

Syntax seems to be ok, but Date(Today()) and your actual date field may not be the same format, so I recommend you to

Date(InvoiceDate)

SV:Re: Set analysis with less than or equal

Thu, 08 Jul 2010 16:37:02 GMT

That looks like the correct syntax to me.

I just tested it out on a sample app and it worked. Here is what I used:

Sum({<Date = {'<=$(=AddMonths(Date(Today()), -12))'}>}Amount)

The only difference seems to be the single quotes, but those shouldn't matter.

I just refreshed and saw Michael's post, formatting could be your problem.

You should check out your dollar sign expansion by creating a chart and inserting the expression, but not giving it a label. Then when the chart is rendered, the label will be your expression with the dollar sign expansion evaluated. For mine, it shows:

Sum({<Date = {'<=7/8/2009'}>}Amount)

That should allow you to verify your date formats and syntax. You may need to add a format string to the Date function.

SV:Re: SV:Re: Set analysis with less than or equal

Fri, 09 Jul 2010 15:46:45 GMT

Thank you all for your assists.

NMiller, I would be greatly happy if you would post your sample app for reference. I don't really understand how to use a chart with dollar sign expansion with my expression.

I am still in version 8.5.

Still I have problem with AddMonths function. I have isolated it and now this expression works:

Sum({$<InvoiceDate={'<=$(Date(Today()))'}>}if(CurrNo=1,Invoice_NetPriceEUR,if(CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE)))

I believe this verifies that InvoiceDate is a date? But still this expression gives me nothing:

Sum({$<InvoiceDate={'<=$(AddMonths(Date(Today()),-12)'}>}if(CurrNo=1,Invoice_NetPriceEUR,if(CurrNo=2,Invoice_NetPriceLocal,Invoice_NetPriceSE)))

I do understand there is something fishy going on but for now I am stuck.

SV:Re: SV:Re: Set analysis with less than or equal

Mon, 12 Jul 2010 14:55:18 GMT

I had an error in the expression - both suggestions worked out correct.