Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
It seems to be working... what is the issue?
Try this
Sum({<APPLY_DATE_AT = {"$(='<=' & Date(Max(TRX_DATE)))"}, TRX_DATE = {"$(='>=' & Date(AddMonths(Max(TRX_DATE),-1)+1) & '<=' & Date(Max(TRX_DATE)))"}>} QUANTITY)
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.
Also this is just a sample in my actual scenario these two dates fields are in two different tables.
But those two are showing up because you have another measure (Sum(QUANTITY)), but the value for the required expression is just 0
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
Look at this document
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
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.