topic Re: How to write Set analysis? in QlikView

How to write Set analysis?

Fri, 31 Oct 2014 10:51:52 GMT

Hi Community,

I have data of all month refer below table.

Emp_no. Emp_designation

1 AB

2 CD

3 EF

4 GH

5 JK

suppose i have select only current month for above table have to show the current month data.so how to write set anaylsis for this condition ?

e.g.

Emp_no. Emp_designation Current_Month_Emp_Designation

1 AB AB

2 CD CD

3 EF EF

thanks in advance.

Re: How to write Set analysis?

MK_QSL — Fri, 31 Oct 2014 10:56:06 GMT

For that you need to have Month column or Date column in your database...

Re: How to write Set analysis?

Fri, 31 Oct 2014 11:07:58 GMT

Hi Manish,

thank u for your qiuk reply.

i have Period column in my data base and i have also try below one expression

sum(<period={"$ (=max(period))"}>Emp_designation)

but it showing Zero valu and also don't know is it right or wrong?

so,need help

Re: How to write Set analysis?

Fri, 31 Oct 2014 11:08:53 GMT

Hi,

Here you dont have the Month column in your data.

Re: How to write Set analysis?

MK_QSL — Fri, 31 Oct 2014 11:10:22 GMT

How your period column data looks like? Kindly provide sample...

Re: How to write Set analysis?

Fri, 31 Oct 2014 11:14:58 GMT

Dear Ishwar,

If you have period in your data base then acc. to qlikview logic If you select a month form period then it will automatically show current month data.

Thanks & Regards

Prince Anand

Re: How to write Set analysis?

PrashantSangle — Fri, 31 Oct 2014 11:16:17 GMT

Hi,

if period is date format then you have to use =Month(max(Period))

Try like this

sum(<period={"$ (=Month(max(Period)))"}>Emp_designation)

Regards,

Re: How to write Set analysis?

Fri, 31 Oct 2014 11:17:33 GMT

Dear Ishwar,

Something is missing in your expression :-

Your expression:- sum(<period={"$ (=max(period))"}>Emp_designation)

Modified expression:- sum({<period={"$ (=max(period))"}>}Emp_designation)

Try this one.

Thanks & Regards

Prince Anand

Re: How to write Set analysis?

jonathandienst — Fri, 31 Oct 2014 11:21:01 GMT

Is period a numeric field?
You are trying to sum the text field [Emp_designation]. Do you mean Count()?
You may need a distinct

HTH

Jonathan

Re: How to write Set analysis?

Fri, 31 Oct 2014 11:27:40 GMT

share the sample data for period coloumn

Re: How to write Set analysis?

jpenuliar — Fri, 31 Oct 2014 11:34:48 GMT

Hi,

have a go with this link:

Set Analysis Wizard for QlikView | qlikblog.at

this has helped me lotsa times.

Re: How to write Set analysis?

Fri, 31 Oct 2014 11:34:49 GMT

Hi everyone,

in my database period column is showing below formate-:

Emp_no. Emp_designation Period

1 AB 10/23/2014

2 CD 10/25/2014

3 EF 10/29/2014

4 GH 9/28/2014

5 JK 9/26/2014

if i select period month is current month suppose (e.g month octomber (10)) then table have to show only octomber month data refer below example formate -:

e.g.

Emp_no. Emp_designation Current_Month_Emp_Designation

1 AB AB

2 CD CD

3 EF EF

so how to bring Third column(i.e. Current_Month_Emp_Designation) according to selection of period.i have tryed below expression

sum(<period={"$ (=max(period))"}>Emp_designation)

but it showing zero value i want output like third column(refer above output )

Re: How to write Set analysis?

MK_QSL — Fri, 31 Oct 2014 11:40:12 GMT

Use below in your script...

Load *, Month(Period) as Month;

Load Emp_no, Emp_designation, Date(Alt(Date#(Period,'M/DD/YYYY'),Date#(Period,'M/D/YYYY'))) as Period Inline

[

Emp_no,Emp_designation,Period

1, AB, 10/23/2014

2, CD, 10/25/2014

3, EF, 10/29/2014

4, GH, 9/28/2014

5, JK, 9/26/2014

];

Now create a Straight Table

Dimension

Emp_no

Emp_designation

Expression

Only({<Month = {'$(=Month(Today()))'}>}Emp_designation)

If you want to count number of Employee for current month, use below

Count({<Month = {'$(=Month(Today()))'}>}Emp_designation)

Count({<Month = {'$(=Month(Today()))'}>}Distinct Emp_designation)

Re: How to write Set analysis?

Fri, 31 Oct 2014 11:54:41 GMT

Thank u everyone for your help,

thank u manish