Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I need your help in Set Analysis, as mention below m using the expression to calculate member count on the basis of date comparison, the syntax which i am using:
this expression is working fine if i select single month, but not working when i use month as a dimension as when i use month as dimension above mention "monthend or monthstart" expression takes only one value i.e. for jan start & end date. Because of this in my output reports it gives the value for jan in all rest of the month as mention below & highlighted in red, the value 128 is for jan only but its coming in all month:
I checked your example.
First there some error in syntax. The correct expression for the set analys is of your example is
COUNT({$<ACTDATE={">=$(=MONTHSTART(MIN([COMMON DATE])))<=$(=MONTHEND(MIN([COMMON DATE])))"},STARTDATE={"<$(=MONTHEND(MIN([COMMON DATE])))"},
COMPLETEDATE={">$(=MONTHEND(MIN([COMMON DATE])))"}>}DISTINCT USER)
where I changed ' in " and COMMON_DATE in [COMMON DATE]
Moreover there is a logical issue:
The expressions computed inside the set analysis (such as your $(=MONTHSTART(MIN([COMMON DATE]))) ) are performed by QLIK regardeless to the row of the table.
Hence for example $(=MONTHSTART(MIN([COMMON DATE])) is always valued as 1/1/2012.
I'm not sure that this is what you want.
thats my issue that its working on selection but i want wrt the row of the table not with selection of month.
Hi Neetu,
Now it's clear to me what you are trying for.
Set analysis expression is executed ONCE for AN OBJECT, i.e. not row wise. That means for your case min/max values for date would be same(max/min of all months) for all the months rather than monthly min/max values. Hence you would not get the result similar to that of IF expression. Therefore, it's safe to say that, for such scenarios IF expression is the solution.
PFA, you would find what i mean.