Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zahidrahim_ocp
Partner - Creator
Partner - Creator

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

23 Replies
zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

I just want to get the sum of the month of September if date selected is 30-SEP-2017 i-e (one month before) and also the apply_date_at should also less than 30-SEP-2017 because of the filter date.

sunny_talwar

It seems to be working... what is the issue?

Capture.PNG

sunny_talwar

Try this

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

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

If you notice the last two lines in APPLY_DATE_AT column those are greater than 30-SEP-2017. APPLY_DATE_AT records should be less than 30-SEP-2017 being the filter applied.

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

Also this is just a sample in my actual scenario these two dates fields are in two different tables.

sunny_talwar

But those two are showing up because you have another measure (Sum(QUANTITY)), but the value for the required expression is just 0

Capture.PNG

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

Dear Sunny,

Thank you very much. You again save my day . Can you please share some material to get a detailed and basic level understanding to these set expressions. As i am very much new to these.

Regards,

Zahid Rahim

sunny_talwar

Look at this document

Set Analysis: syntaxes, examples

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

Dear Sunny,

Thank you for everything.

With this set expressions our amounts issue resolved perfectly but other dimensions cannot be selected if they don't have any transactions at the applied filter date. This is the issue i am facing with almost every analysis What can be the other workaround or methodology for such development.

Regards,

Zahid Rahim

sunny_talwar

I don't completely follow your concern here... would you be able to elaborate on this part

With this set expressions our amounts issue resolved perfectly but other dimensions cannot be selected if they don't have any transactions at the applied filter date.