Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis with less than or equal

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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
Not applicable
Author

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

Not applicable
Author

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

Miguel_Angel_Baeyens

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)


Not applicable
Author

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.

Not applicable
Author

Thank you all for your assists. Yes

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.

Not applicable
Author

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