topic Set analysis in QlikView

Set analysis

Tue, 18 Mar 2014 09:09:32 GMT

Hi all, need help,

i have a set - COUNT({<StatusHistory.YearMonth = {">=$(=MonthStart(min(StatusHistory.YearMonth)))"}>}distinct SabID) , its counts sabid at month start and works fine, now i wan to count sabid of next month monthstart,

COUNT({<StatusHistory.YearMonth = {">=$(=MonthStart(min(StatusHistory.YearMonth),+1))"}>}distinct SabID) and its returs me 0..

please help

Re: Set analysis

Tue, 18 Mar 2014 09:14:07 GMT

>= MonthEnd instead of Monthstart+1

Re: Set analysis

MK_QSL — Tue, 18 Mar 2014 09:14:22 GMT

COUNT({<StatusHistory.YearMonth = {'>=$(=MonthStart(AddMonths(min(StatusHistory.YearMonth),1))'}>}Distinct SabID)

Re: Set analysis

CELAMBARASAN — Tue, 18 Mar 2014 09:22:18 GMT

What is the value format in YearMonth Field?

How you created this field?

Re: Set analysis

MK_QSL — Tue, 18 Mar 2014 09:26:05 GMT

COUNT({<StatusHistory.YearMonth = {'>=$(=Date(MonthStart(Min(StatusHistory.Date),1),'MMM YYYY'))'}>}Distinct SabID)

Change 'MMM YYYY' according to your YearMonth format... i.e. YYYY MMM or YY MMM

Re: Set analysis

Tue, 18 Mar 2014 10:27:41 GMT

its still return my 0...

Re: Set analysis

PrashantSangle — Tue, 18 Mar 2014 10:42:07 GMT

Hi,

I am just modifying your set analysis because it is almost close.

COUNT({<StatusHistory.YearMonth = {">=$(=MonthStart(AddMonths(min(StatusHistory.YearMonth),+1)))"}>}distinct SabID)

Regards,

Re: Set analysis

Tue, 18 Mar 2014 11:13:27 GMT

thanks for set analysis, but i still get 0.. there is 2 pics, firs what i get now, and secont what i need:

Re: Set analysis

MK_QSL — Tue, 18 Mar 2014 11:18:26 GMT

It will be better if you load your sample apps or sample data file.

Re: Set analysis

Tue, 18 Mar 2014 11:37:47 GMT

its impossible, apps ~600mb...

Re: Set analysis

MK_QSL — Tue, 18 Mar 2014 11:39:23 GMT

You can definitely create dummy data say 10-20 lines in excel file..

Re: Set analysis

PrashantSangle — Tue, 18 Mar 2014 11:45:59 GMT

Hi,

Actually there is very small mistake

try this,

COUNT({<StatusHistory.YearMonth = {">=$(=MonthName(MonthStart(AddMonths(min(StatusHistory.YearMonth),+1))))"}>}distinct SabID)

MonthStart() returns complete date like 1-5-2013

but you StatusHistory.YearMonth is in format of Kov 2010 i.e (Mar 2010)

therefore you have convert above expression using Monthname()

Hope this will help you,

Regards,

Re: Set analysis

Tue, 18 Mar 2014 12:04:53 GMT

attached to my firs post on top

Re: Set analysis

PrashantSangle — Tue, 18 Mar 2014 12:06:24 GMT

Hi,

Is this solution work???

Re: Set analysis

PrashantSangle — Tue, 18 Mar 2014 12:07:42 GMT

Hi,

Did you try my solution??

Re: Set analysis

Tue, 18 Mar 2014 12:19:44 GMT

I missed yours replay, sorry.. but yours solution not work..

Re: Set analysis

Tue, 18 Mar 2014 12:33:38 GMT

count({<StatusHistory.YearMonth={">=$(=addmonths(monthstart(min(StatusHistory.YearMonth)),1)"}>}distinct SabID)

Re: Set analysis

CELAMBARASAN — Tue, 18 Mar 2014 12:37:39 GMT

I'm guessing, if you provide how the YearMonth field is created. well that gives clear idea solve your problem.

Regards,

Celambarsan