Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

zahidrahim_ocp
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

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

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)

zahidrahim_ocp
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

Re: Simple Set Expression for two dates

My bad, try this

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

zahidrahim_ocp
Contributor

Re: Simple Set Expression for two dates

Same Result Not showing anything.

pradosh_thakur
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

Pradosh

zahidrahim_ocp
Contributor

Re: Simple Set Expression for two dates

Dear Pradosh,

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

Re: Simple Set Expression for two dates

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

zahidrahim_ocp
Contributor

Re: Simple Set Expression for two dates

Dear Sunny,

PFA

Regards,

Zahid Rahim

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?

Community Browser