topic Re: Set analysis in QlikView

Set analysis

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

Hi Experts,

Can any one please help me on below requirement.

I have a Due date field in DD/MM/YYYY format. From that field based on Weekday need to derive the below Measure.

Count(Distinct issuenum) where duedate>= start of week(Monday) and <=end of week(sunday)

how to add the where condition on set analysis. Please help me on this.

Thanks in advance.

Re: Set analysis

Nagaraju_KCS — Wed, 28 Mar 2018 06:29:55 GMT

try this...

Count(Distinct issuenum) where

duedate>= weekStart(duedate) and <=Weekend(duedate)

=Sum({<OrderDate={'>=$(=WeekStart(OrderDate))<=$(=WeekEnd(OrderDate))'}>}OrderSalesAmount)

Re: Set analysis

PrashantSangle — Wed, 28 Mar 2018 06:35:17 GMT

try

count({<dateField={">=$(=date(weekstart(duedate)))<=$(=date(weekend(duedate)))"}>} issuenum)

Regards,

Re: Set analysis

shiveshsingh — Wed, 28 Mar 2018 06:38:10 GMT

May be this

Count({<Weekday(Date(DueDate,'DD')) = {">=$(=weekday(WeekStart(DueDate,0,0)))<=$(=weekday(Weekend(DueDate,0,0)))"}>}Distinct issuenum)

Re: Set analysis

shiveshsingh — Wed, 28 Mar 2018 08:17:58 GMT

What's the format of your Duedate?

Re: Set analysis

bhavvibudagam — Wed, 28 Mar 2018 08:19:23 GMT

Its MM/DD/YYYY

Re: Set analysis

shiveshsingh — Wed, 28 Mar 2018 08:27:25 GMT

=Count(DISTINCT{<DueDate ={">=$(=Date(WeekStart(DueDate,0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(DueDate,0,0),'MM/DD/YYYY'))"}>}issuenum)

Try this

Re: Set analysis

bhavvibudagam — Wed, 28 Mar 2018 08:35:06 GMT

Hi Shivesh,

Now getting 0

Thanks

Re: Set analysis

shiveshsingh — Wed, 28 Mar 2018 08:50:58 GMT

Please share your app once

Re: Set analysis

bhavvibudagam — Wed, 28 Mar 2018 09:02:33 GMT

Hi Shivesh,

Sorry. I cant share the app its a large data file.

Re: Set analysis

shiveshsingh — Wed, 28 Mar 2018 09:07:49 GMT

=Count(DISTINCT{<Date(DueDate,'MM/DD/YYYY') ={">=$(=Date(WeekStart(DueDate,0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(DueDate,0,0),'MM/DD/YYYY'))"}>}issuenum)

Please share o/p for =Date(WeekStart(DueDate,0,0),'MM/DD/YYYY') & =Date(Weekend(DueDate,0,0),'MM/DD/YYYY')

Re: Set analysis

sasiparupudi1 — Wed, 28 Mar 2018 11:20:35 GMT

I think you need a max here

Count(DISTINCT{<DueDate ={">=$(=Date(WeekStart(Max(DueDate),0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(Max(DueDate),0,0),'MM/DD/YYYY'))"}>}issuenum)

Re: Set analysis

PrashantSangle — Thu, 29 Mar 2018 07:13:02 GMT

Hi shiveshsingh‌,

In set analysis, you can not format left hand side field of = symbol

=Count(DISTINCT{<Date(DueDate,'MM/DD/YYYY') ={">=$(=Date(WeekStart(DueDate,0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(DueDate,0,0),'MM/DD/YYYY'))"}>}issuenum)

Regards,

Re: Set analysis

shiveshsingh — Thu, 29 Mar 2018 07:39:01 GMT

Thanks Prashant for correction.

Re: Set analysis

Chanty4u — Thu, 29 Mar 2018 09:21:58 GMT

is that table box use aggr to work it out

=aggr(only(DISTINCT{<Date(DueDate,'MM/DD/YYYY') ={">=$(=Date(WeekStart(DueDate,0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(DueDate,0,0),'MM/DD/YYYY'))"}>}issuenum),issuenum)

Re: Set analysis

Anonymous — Thu, 29 Mar 2018 09:24:12 GMT

Hi,

Check with below condition. In your case you need to use the fields that your using inside the table in set analysis.

Count(DISTINCT{<Field1,Field2,DueDate ={">=$(=Date(WeekStart(Max(DueDate),0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(Max(DueDate),0,0),'MM/DD/YYYY'))"}>}issuenum)

Thanks,

Venkata Sreekanth