Condition in set analysis.

[VamsiBhagavathula1]

Hello Guys,

Could someone please tell me how to do a field comparison inside set analysis based on the Qlik calendar?

in my case, one customer has multiple refunds for a order on different dates. Qlik calendar is giving me multiple rows for the refund dates(like cross join) . Out of this i need only the rows where calendar date matches refund date.  After this , I need to make some set analysis.

So, mainly, I want to achieve this Date comparison of calendar date & refund date in set analysis and perform count & sum aggregations.

Please see the screenshot attached file of QVF & screenshot. Please let  me know  how  we can achieve it set analysis.

Thanks,

Vamsi

[BrunPierre]

Count(DISTINCT {$<Date={"=Date=RefundDate"},DateType={'REFUNDS'}>} REF_AMT)

or

Create a flag within the load statement if both date fields originate from the same table.

If(Date = RefundDate, 1, 0) as Flag

Then you could do this instead

Count(DISTINCT {$<Flag={1},DateType={'REFUNDS'}>} REF_AMT)

[VamsiBhagavathula1]

Thanks @BrunPierre . It doesn't work . Its give me all 0's. Have you tried it within qvf file attached??

[BrunPierre]

---

Count(DISTINCT {$<Date={"=Date=RefundDate"},DateType={'REFUNDS'}>} REF_AMT)

---

[VamsiBhagavathula1]

@BrunPierre : Thanks again for checking this. It does not  work for  me

Count(DISTINCT {$<Date={"=Date=RefundDate"},DateType={'REFUNDS'}>} REF_AMT)

I cannot create flag as well because Date comes from Qlik Torbien calendar and RefundDate comes from Refund table. as they are not coming from same table, So i cannot create a flag. If possible can  you try with my qvf file attached in this post

[VamsiBhagavathula1]

@BrunPierre : i think it didn't worked on your side either. because in the screenshot which you uploaded you get only 1 row for DEF. What happend to other C_ID"ABC"??

[BrunPierre · Accepted solution]

As below with Include zero values ​​unchecked.

=Count(Aggr(If(Date = RefundDate and DateType='REFUNDS', REF_AMT), YYYYMM, C_ID, REASON, DateType, RefundDate, Date))

[VamsiBhagavathula1]

@BrunPierre : wow, Thanks a lot . It seems to be working for count. how could i do sum with same conditions. but in SUM i need to sum up refund amounts. Can you please help here as well ??

[BrunPierre]

=Sum(Aggr(If(Date = RefundDate and DateType='REFUNDS', [Refund Amount]), YYYYMM, C_ID, REASON, DateType, RefundDate, Date))

[vinieme12]

i would prefer creating a canonical calendar to simplify such expression,

this can be simplified to something like below

=sum({<datetype={'refunddate'}>}refundamount)

https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578