topic Re: Set analysis in QlikView

Set analysis

Tue, 04 Mar 2014 07:00:15 GMT

Hi All, nedd help, i have a table :

AbonementID	AbonementEndDate
1	2013.03.03
2	2013.03.04
3	2013.03.06
4	2013.02.04
5	2013.02.05
6	2013.04.06
7	2013.02.07
8	2013.03.08
9	2013.03.09
10	2013.04.10
11	2013.04.11
12	2013.06.12

i need 2 set analysis, one: i need count all active abonements at the start of month, for examp: at 1 of march i have 9 active abonements,

second set analysis: i need count all active adonements at the month end(march 31), expecting result =3

please help

Re: Set analysis

MK_QSL — Tue, 04 Mar 2014 07:04:59 GMT

COUNT({<AbonementEndDate = {'$(=MonthStart(Today()))'}>}AbonementID)

COUNT({<AbonementEndDate = {'$(=MonthEnd(Today()))'}>}AbonementID)

Update : Please check enclosed file of similar functionality

Re: Set analysis

Tue, 04 Mar 2014 07:25:33 GMT

Thans for fast replay, correct my if i wrong, for sum its working great, but i think for count is working wrong.. its counts only those abonements ho have abonements end date at monthstart and monthend.

Re: Set analysis

MK_QSL — Tue, 04 Mar 2014 07:30:06 GMT

What is your requirement?

What ID you want to count ? i.e. Only at the End of Month or Start of Month or something else?

May be I misunderstood your question.

Re: Set analysis

Tue, 04 Mar 2014 07:44:38 GMT

i need count abonement id,my requirement is: 1 set analysis, count all active abonements when we have a monthstart, by my table result would be:

AbonementID	AbonementEndDate
1	2014.03.03
2	2014.03.04
3	2014.03.06


6	2014.04.06

8	2014.03.08
9	2014.03.09
10	2014.04.10
11	2014.04.11
12	2014.06.12

second set analysis count all active abonements when we have a monthend, result:

AbonementID	AbonementEndDate





6	2014.04.06



10	2014.04.10
11	2014.04.11
12	2014.06.12

in my first post the year is wrong, should be 2014, i think that is you mislead, sorry

Re: Set analysis

MK_QSL — Tue, 04 Mar 2014 07:47:33 GMT

It is difficult for anyone to give you result without knowing your actual data.

Can you post your sample apps or sample data with desired output.

This will be easiest way to give you straight forward answer..

Re: Set analysis

Tue, 04 Mar 2014 08:04:20 GMT

i found solution, little modified yours set analysis and its works:

COUNT({<Abonement.End = {">=$(=MonthStart(Today()))"}>}distinct SabID)

and

COUNT({<Abonement.End = {">=$(=MonthEnd(Today()))"}>}distinct SabID)

with my english i cant clearly explaint what i waint i think...sory and thanks for help

Re: Set analysis

MK_QSL — Tue, 04 Mar 2014 08:09:46 GMT

Oh... you want to count from MonthStart and From MonthEnd...

As far as you got your answer, I am happy.

English is not a barrier to learn QlikView... Enjoy....

Re: Set analysis

Tue, 04 Mar 2014 08:41:43 GMT

one more question, is it posible to remove today() funtion from set analysis? if i want to analyse date in the past, for example, 2013 year and december month?

Re: Set analysis

MK_QSL — Tue, 04 Mar 2014 09:08:29 GMT

You can remove Today() and make more dynamic like below

Considering that you have List BOX with Field Date...

You can use below ...

=SUM({<Date = {'>=$(=MonthStart(Max(Date)))'}>}Sales)

Re: Set analysis

Tue, 04 Mar 2014 09:54:55 GMT

thanks again, and one more question , ok, if i select on master calendar year and month its show me correct values, but if is no selected in the calendar its show me a wrong values..why? i need . when i activating sheet, it show me all tendency by year and month of abonements..now my set analysis is:

COUNT({<Abonement.End = {">=$(=MonthStart(Max(Date)))"}>}distinct SabID)

and

COUNT({<Abonement.End = {">=$(=MonthEnd(Max(Date)))"}>}distinct SabID)

Re: Set analysis

MK_QSL — Tue, 04 Mar 2014 10:05:24 GMT

If you will not select anything, you will get result

for 1st Expression This Month TO DATE data...

for 2nd Expression ,,, will give you null or o

Re: Set analysis

Tue, 04 Mar 2014 10:11:29 GMT

so how can i modified set analysis to avoid this problem?

Re: Set analysis

MK_QSL — Tue, 04 Mar 2014 10:19:20 GMT

This depends on what outcome you want?

I still unclear about your requirements !

Re: Set analysis

Tue, 04 Mar 2014 11:15:13 GMT

now its calculate only if i select year and month in master calendar, and calculate id only in one month, i want without any selection in master calendar calculale id in each available month