New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

Simple Set Expression for two dates

Dear Qlik Experts,

I want to write a set expression based on two conditions but it is not working. I want to see sum for records which have APPLY_DATE_AT is less than or equal to selected date in a variable (filter date) and TRX_DATE is Greater than previous month and less than the date selected.

Sum(

{<APPLY_DATE_AT = {"\$(='<=' & Date(Max(TRX_DATE)))"}>*<TRX_DATE = {"\$(='>=' & Date(AddMonths(TRX_DATE,-1)+1))"}>*<TRX_DATE = {"\$(='<=' & Date(TRX_DATE))"}>} AMOUNT_TAKEN

)

Regards,

Zahid Rahim

1 Solution

Accepted Solutions
MVP

Re: Simple Set Expression for two dates

Try this

Sum({<APPLY_DATE_AT = {"\$(='<=' & Date(Max(TRX_DATE)))"}, TRX_DATE = {"\$(='>=' & Date(AddMonths(Max(TRX_DATE),-1)+1) & '<=' & Date(Max(TRX_DATE)))"}>} QUANTITY)

23 Replies
MVP

Re: Simple Set Expression for two dates

May be this

Sum({<APPLY_DATE_AT = {"\$(='<=' & Date(Max(TRX_DATE)))"}>*<TRX_DATE = {"\$(='>=' & Date(AddMonths(Max(TRX_DATE),-1)+1)) & '<=' & Date(Max(TRX_DATE)))"}>} AMOUNT_TAKEN)

Contributor

Re: Simple Set Expression for two dates

Dear Sunny,

Thank you for the reply. But it is not showing anything at all.

Regards,

Zahid Rahim

MVP

Re: Simple Set Expression for two dates

Sum({<APPLY_DATE_AT = {"\$(='<=' & Date(Max(TRX_DATE)))"}>*<TRX_DATE = {"\$(='>=' & Date(AddMonths(Max(TRX_DATE),-1)+1) & '<=' & Date(Max(TRX_DATE)))"}>} AMOUNT_TAKEN)

Contributor

Re: Simple Set Expression for two dates

Same Result Not showing anything.

Valued Contributor III

Re: Simple Set Expression for two dates

May be this

=Sum( { <APPLY_DATE_AT = {"\$(='<=' & Date(Max(TRX_DATE)))"}> * <TRX_DATE = {"\$(='>=' & Date(AddMonths(TRX_DATE,-1)+1) &  '<=' & Date(TRX_DATE))"}>} AMOUNT_TAKEN )

and can you check what is the default date format and the format of you date in the dashboard

regards

Contributor

Re: Simple Set Expression for two dates

Not working.

if i remove first condition it is showing records with additional apply_date_at.

=Sum( {  <TRX_DATE = {"\$(='>=' & Date(AddMonths(TRX_DATE,-1)+1) &  '<=' & Date(TRX_DATE))"}>} AMOUNT_TAKEN )

Formats are same.

regards,

Zahid

MVP

Re: Simple Set Expression for two dates

Would you be able to share a sample to look at?

Contributor

Dear Sunny,

PFA

Regards,

Zahid Rahim

MVP

Re: Simple Set Expression for two dates

It would be helpful to know what the expected output you are looking to get from this chart?