23 Replies Latest reply: Oct 16, 2017 7:25 AM by Sunny Talwar

# 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

• ###### 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)

• ###### 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

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

• ###### Re: Simple Set Expression for two dates

Same Result Not showing anything.

• ###### 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

• ###### 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

• ###### Re: Simple Set Expression for two dates

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

• ###### 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?

• ###### Re: Simple Set Expression for two dates

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.

• ###### 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)

• ###### Re: Simple Set Expression for two dates

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

• ###### Re: Simple Set Expression for two dates

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.

• ###### Re: Simple Set Expression for two dates

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

• ###### Re: Simple Set Expression for two dates

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

• ###### Re: Simple Set Expression for two dates

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

• ###### Re: Simple Set Expression for two dates

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

• ###### Re: Simple Set Expression for two dates

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.

• ###### Re: Simple Set Expression for two dates

Dear Sunny,

PFA the same qvf with item_sid column.

If we select date 30-SEP-2017. Total is calculating perfectly as per your provided expression. Now if we open Item_sid filter we can see only Item_sid 1 is selectable and we can have total for item_sid =1 as well. (Reason Item_sid =1 have transaction on 30-SEP-2017)

But what about the other Item_sids 2 and 3 we cannot see totals for these two sids.

Regards,

Zahid Rahim

• ###### Re: Simple Set Expression for two dates

That's just how Qlik Sense and QlikView's association model works... based on your selection in one field, only those fields which have association with the other fields selection will be available for selection.... So, in you case only ITEM_SID = 1 is associated with 9/30/2017.... Don't recommend to break this association model, but if you really want, you can look into triggers.... the idea will be to select the last 30 days based on one selection you make in TRX_DATE field.... Unfortunately, I am new to Qlik Sense functionalities, so I won't be able to offer any help with the triggers, but if you want I can show you how this would work in QlikView

• ###### Re: Simple Set Expression for two dates

Dear Sunny,

Thank you for all these details. So we cannot apply a further filter. OK but it becomes very difficult to find a dimension like customer or product name from thousands of records in a table or chart. Is there any solution to simply find a specific value.

Regards,

Zahid Rahim

• ###### Re: Simple Set Expression for two dates

One way would be to create The As-Of Table and then make selection in the as of date and then it can work....