topic Re: Stuck in set Analysis in QlikView

Stuck in set Analysis

sarfaraz_sheikh — Mon, 08 Jun 2015 12:50:54 GMT

Dear All,

I am using below expression in my formats...but i am not getting any results by using the same expression.

count(DISTINCT {< month_year={">$(=vStart_Month)"}>} D_SI_NO

When i remove the greater_than sign (>) from expression then it is work ...once i attribute (>) symbol with expression its not working.

month_year is field contains value like ....Apr 2015, May 2015 ,,,Jun 2015 etc. and i want to calculate for specific periods.

vStart_Month == is variable contans this...... MonthName(YearStart(Today()-1,0,4)) ............expression

I dont know where i am doing wrong ...can anybody suggest me what should i have to do .

Sarfaraz

Re: Stuck in set Analysis

sunny_talwar — Mon, 08 Jun 2015 12:53:04 GMT

Try this:

=Count(DISTINCT {<month_year={"$(='>' & MonthName(YearStart(Today()-1,0,4)))"}>} D_SI_NO)

Re: Stuck in set Analysis

sarfaraz_sheikh — Mon, 08 Jun 2015 13:00:41 GMT

Hey its not working........I have tried...can you please suggest another option ...

Sarfaraz

Re: Stuck in set Analysis

jonathandienst — Mon, 08 Jun 2015 13:03:15 GMT

Have your verified that you are getting what you expect in vStart_Month?

If the month_year field also a dual value like vStart_Month?

Strictly speaking DISTINCT should come after the set expression, although it currently works it is before.

Re: Stuck in set Analysis

sunny_talwar — Mon, 08 Jun 2015 13:07:50 GMT

Like jonathan dienst suggested, I would check if month_year is a date field or text field? If it is text, the above formula won't work (but when you mentioned that the without > it was working, it made me assume that month_year is a date field). Is there anyway you can post a reduced version of your document? or may be a sample?

Best,

Sunny

Re: Stuck in set Analysis

sarfaraz_sheikh — Mon, 08 Jun 2015 13:09:08 GMT

Yes i have verified i am getting as expected value in vStart_Month variable which is "Apr 2015" ....and i want to calculate the count which are greater that apr 2015 ...

I tried to use DISTICT after an expression ....but these also did not work ...

Can you please suggest any example here for my understanding.

sarfaraz

Re: Stuck in set Analysis

sarfaraz_sheikh — Mon, 08 Jun 2015 13:11:39 GMT

Dear,

i am using version 11 and month_year is date field......

sarfaarz

Re: Stuck in set Analysis

sunny_talwar — Mon, 08 Jun 2015 13:16:46 GMT

So this in a text box (='>' & MonthName(YearStart(Today()-1,0,4))) gives me >Apr 2015. So you are trying to count everything from May 2015 onwards. Is that correct? If you want to include Apr 2015 you need to change your expression to

=Count(DISTINCT {<month_year={"$(='>=' & MonthName(YearStart(Today()-1,0,4)))"}>} D_SI_NO)

Re: Stuck in set Analysis

rubenmarin — Mon, 08 Jun 2015 13:21:56 GMT

Hi Sarfaraz, I usually avoid using fields with month names on set analisys when they will be compared using '>' or '<', instead use a field in numeric format or a date wich has a number format for month, ie: DD/MM/YYYY, YY-DD-MM

Re: Stuck in set Analysis

sarfaraz_sheikh — Mon, 08 Jun 2015 13:25:48 GMT

Can you keep any example here for my understanding so that would be more helpful for me ..

Sarfaraz

Re: Stuck in set Analysis

rubenmarin — Mon, 08 Jun 2015 14:02:28 GMT

Hi, just create another field in your calendar with year-Month as number, or as a date, ie:

Year(Date) & Num(Month(Date), '00') as YearMonthNum,

Then you can use:

count(DISTINCT {<YearMonthNum={">$(vStart_YearMonth)"}, Month>} D_SI_NO

vStart_YearMonth: =Min(YearMonthNum)

Also can be done loading Year-Month as date:

MonthStart(Date) as YearMonthDate // This will allow you to use date functions like AddMonths()

Re: Stuck in set Analysis

sarfaraz_sheikh — Mon, 08 Jun 2015 14:19:04 GMT

Thnaks Ruben ...your solution is working as expected ....Tnaks a lot

Sarfaraz