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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
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.