Solved: Re: Stuck in set Analysis - Qlik Community

sarfaraz_sheikh · ‎2015-06-08

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

rubenmarin · ‎2015-06-08

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()

View solution in original post

sunny_talwar · ‎2015-06-08

Try this:

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

sarfaraz_sheikh · ‎2015-06-08

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

Sarfaraz

jonathandienst · ‎2015-06-08

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

sunny_talwar · ‎2015-06-08

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

sarfaraz_sheikh · ‎2015-06-08

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

sarfaraz_sheikh · ‎2015-06-08

Dear,

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

sarfaarz

sunny_talwar · ‎2015-06-08

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)

rubenmarin · ‎2015-06-08

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

sarfaraz_sheikh · ‎2015-06-08

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

Sarfaraz