topic Re: Set Analysis unable to exclude common record in QlikView

Set Analysis unable to exclude common record

Karim_Khan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I wanted to compare two dates value and need to show only those records which are not present in previous month last day and

present in current month last day i.e i wanted to exclude all common record between these two dates and put only non-matching records.

I have tried the below expression

varibale define

for getting last day of current month

vLastDayPrevMon=MonthEnd(AddMonths($(vFromDate), -1))

LastDayCurrMon=MonthEnd($(vToDate))

=if(Date='$(vLastDayPrevMon)' AND Date='$(vLastDayCurrMon)',ID)

stalwar1‌

Re: Set Analysis unable to exclude common record

sunny_talwar — Thu, 07 Jun 2018 10:37:30 GMT

For the sample provided... this seems to work

Avg({<DESC = {"=Count(DISTINCT {<Date = {[$(=Date(Max(Date)))], [$(=Date(Floor(MonthEnd(Max(Date), -1))))]}>} Date) = 1"}>} 1)

Re: Set Analysis unable to exclude common record

Karim_Khan — Mon, 11 Jun 2018 11:27:49 GMT

Hi Sunny Thanks for ur help a always bt still some challenges while trying to implement it with my dynamic calendar date

this exprssion in working for dynamic dates but while m trying to combine ur also its giving null values

=IF(Date='>$(=Date(vFromDate))<$(=Date(vToDate))',

Avg({<ID= {"=Count(DISTINCT {<Date= {[$(=Date(vFromDate))], [$(=Date(Floor(MonthEnd(vToDate))))]}>} Date) = 1"}>} 1))

Re: Set Analysis unable to exclude common record

sunny_talwar — Mon, 11 Jun 2018 11:33:37 GMT

Can you share a sample to show the issue?

Re: Set Analysis unable to exclude common record

Karim_Khan — Thu, 21 Jun 2018 12:16:06 GMT

sorry have missed one filter if will apply then its working

=Avg({<ID = {"=Count(DISTINCT {<DATE = {[$(=Date(Floor(MonthEnd(Max({<ID>}DATE), -1))))]}>} DATE) + Sum(0) = 0"}>} 1)

Thank You so much stalwar1

Re: Set Analysis unable to exclude common record

sunny_talwar — Thu, 21 Jun 2018 12:19:57 GMT

Hahaha awesome and you deleted the whole post and rewarded yourself the correct response. Great going .

Re: Set Analysis unable to exclude common record

Karim_Khan — Thu, 21 Jun 2018 12:51:41 GMT

m really sorry by mistakenly i selected that will rectify it now only. and have deleted that screenshot only.

Re: Set Analysis unable to exclude common record

Karim_Khan — Thu, 21 Jun 2018 13:02:08 GMT

stalwar1‌ sorry it was mistake and unintentionally

Re: Set Analysis unable to exclude common record

Karim_Khan — Fri, 03 Aug 2018 10:48:56 GMT

Hi Sunny,

Can u pls help me out to understand the below expression

=Avg({<ID = {"=Count(DISTINCT {<DATE = {[$(=Date(Floor(MonthEnd(Max({<ID>}DATE), -1))))]}>} DATE) + Sum(0) = 0"}>} 1)

Re: Set Analysis unable to exclude common record

sunny_talwar — Fri, 03 Aug 2018 10:52:38 GMT

What do you not understand?

Re: Set Analysis unable to exclude common record

Karim_Khan — Fri, 03 Aug 2018 12:28:51 GMT

This entire value

Count(DISTINCT {<DATE = {[$(=Date(Floor(MonthEnd(Max({<ID>}DATE), -1))))]}>} DATE) + Sum(0)

Re: Set Analysis unable to exclude common record

sunny_talwar — Fri, 03 Aug 2018 12:33:20 GMT

Counting DISTINCT Dates where Date = last date of the previous month.... and seems like adding Sum(0) to avoid nulls because of the set analysis on date....