topic Re: Count Distinct with Set Analysis in QlikView

Count Distinct with Set Analysis

Thu, 19 Mar 2015 22:19:12 GMT

Hi,

I need some help with an expression of Count Distinct with Set Analysis, anyone can help?

The goal is calculate total number of jobs based on selected period.

For example, if user selects Dec 14, then count total number of jobs for Dec 13

if user selects Oct, Nov and Dec 14, then count total number of jobs for Oct, Nov and Dec 13

Here is my script below, non of them is working. please verify me if I am wrong. Thanks

Solution 1:

=count({$<Date=, Year=, Quarter=, Week=,

Period = {">=$(=monthname(AddYears(min(MC_Period),-1))) <=$(=monthname(AddYears(max(MC_Period),-1)))"}

DISTINCT JobNum)

Solution 2:

=count({<Period=, Date=, Year=, Quarter=,Week=>}if(Period >= vPeriodLYFromMin and Period <= vPeriodLYTo, JobNum,0))

vPeriodLYFromMin = monthname(AddYears(min(MC_Period),-1))

vPeriodLYTo = =monthname(AddYears(max(MC_Period),-1))

Re: Count Distinct with Set Analysis

Thu, 19 Mar 2015 22:34:16 GMT

Hi Derek:

What is 'MC_Period'?

Is that a field in an ad hoc table which is not linked to the data?

Thanks

Re: Count Distinct with Set Analysis

nico_ilog — Thu, 19 Mar 2015 22:39:00 GMT

Good Day Derek,

Maybe i;m missing the scenario?

But why do you want to make it so complicated with SET ANALYSIS?

Depending on you data structure you could just use a Pivot Table and Utilize your:

- Year

- Month

- Day

Fields as the dimensions, and simply use the expression of Count(Jobs)

But, like i said, maybe im missing it? Would you mind giving some more info?

I also see you mentioned that the Day you want to calculate is the Day smaller than the selected date? is that a mistake? or is that what you want to achieve?

Rgds,

Nico

Re: Count Distinct with Set Analysis

Thu, 19 Mar 2015 22:42:34 GMT

Hi Derek:

Maybe try to transform the periods into numbers (maybe using AutoNum) and then do the comparison.

Using number can also replace the AddYears with simply PeriodNum - 12.

Thanks

Re: Count Distinct with Set Analysis

prieper — Thu, 19 Mar 2015 22:43:53 GMT

Might make sense to work with rolling monthes (Year * 12 + Monthnum) and then create your expression like

COUNT(${<RollingMonth = {RollingMonth -12}>} Jobs)

HTH Peter

Re: Count Distinct with Set Analysis

Thu, 19 Mar 2015 22:44:20 GMT

I think he wants to show the number in text box.

Like this year v.s. last year comparison

Re: Count Distinct with Set Analysis

nico_ilog — Thu, 19 Mar 2015 22:46:03 GMT

Alternatively,

You could just manipulate your Pivot to get this output??

Re: Count Distinct with Set Analysis

ramoncova06 — Thu, 19 Mar 2015 23:24:21 GMT

tweeked it a little, the main issue with your set analysis is that you are ignoring the "Period" and then trying to use it again, instead create a "Period1" with the same info that "Period" has and add that one inside of the period set, that will work, I also assumed that min is just a date also

Solution 2:

vPeriodLYFromMin = min(MC_Period))

vPeriodLYTo = max(MC_Period)

=count({<Period1 = {'>=$(=(vPeriodLYFromMin))<=$(=(vPeriodLYTo))'}, Period=, Date=, Year=, Quarter=,Week=>} JobNum)

Re: Count Distinct with Set Analysis

Thu, 19 Mar 2015 23:29:20 GMT

MC_Period is just Period

Period = MonthName(TempDate)

Derek Cheng

Business Analyst

Toll Global Forwarding

T: +61 3 9982 8066

M: +61 466 525 543

Re: Count Distinct with Set Analysis

Thu, 19 Mar 2015 23:33:32 GMT

It is a column in straight table, just want to compare the job number count for current selected period and same period last year.

Re: Count Distinct with Set Analysis

Thu, 19 Mar 2015 23:46:08 GMT

Are you able to explain where did I do wrong with the first solution?

Re: Count Distinct with Set Analysis

ramoncova06 — Fri, 20 Mar 2015 00:21:12 GMT

I would need to see the data before I could tell you, though just from looking at it you are comparing the monthname what is the format ? MMYYYY, or MMMYYYY?

Re: Count Distinct with Set Analysis

Fri, 20 Mar 2015 02:51:41 GMT

Hi Ramon,

The format for monthname is MMMYYYY

Re: Count Distinct with Set Analysis

prieper — Fri, 20 Mar 2015 08:29:59 GMT

Think that you need something like the attached

=COUNT({1 <MyDate_rM = {">=$(=MIN(MyDate_rM) -12) <=$(=MAX(MyDate_rM) -12)"} >} DISTINCT Expression1)

HTH Peter

Re: Count Distinct with Set Analysis

Fri, 16 Oct 2015 17:07:22 GMT

On the follow up with the same set analysis for the count, I have been struggling with a simple with Date and count with emplid:

=COUNT({<Years=, Month=, Quarter=, Week=, EFFDT =,Date={">=$(=Num ( MonthStart (Max(Date))))<=$(=Max(Date))"}>} DISTINCT(EMPLID))

Date is : Floor(trim(DATE(EFFDT,'MM/DD/YYYY'))) as Date,

any help is appreciated...I get the numbers but totally wrong...am I missing something?