topic Re: Set analysis count in QlikView

Set analysis count

Tue, 19 Mar 2013 17:10:37 GMT

I have two date fields DATE1 and DATE2 and I am intending to count ID. How can I write below expression in set analysis.

COUNT(IF(Date1=Date2, ID))

Re: Set analysis count

alexandros17 — Tue, 19 Mar 2013 17:18:22 GMT

Set analysis doesn't apply to test on fields but on fixed values

So you can load in your script a new field TEST

Load

....

if(Data1=Data2,1,0) as Test

from ...

then you expression will be

Count({$ <Test={1}>} ID)

Re: Set analysis count

srinivasa1 — Tue, 19 Mar 2013 17:56:03 GMT

Try as below.

count({$<Date1= Date2>} ID)

Re: Set analysis count

Tue, 19 Mar 2013 18:58:20 GMT

Calendar is generated by DATE1. For some reason DATE1 = DATE2 doesnt seem to work. Format is dd/mm/yyyy hh:mm:ss. Checked the format too. Acting like a beast.

Re: Set analysis count

srinivasa1 — Tue, 19 Mar 2013 19:14:29 GMT

What value coming in date1 and date2? check by adding both in list box and compare. also check trim and null value

Re: Set analysis count

Tue, 19 Mar 2013 21:13:58 GMT

Did that too. No null values. When I select dates from list box the cchart gives right numbers but not when in an expression.

Re: Set analysis count

kaushiknsolanki — Wed, 20 Mar 2013 07:00:33 GMT

Hi,

Try this.

Load

....

if(Date(date#(Data1,'DD/MM/YYYY hh:mm:ss'))=Date(date#(Data2,'DD/MM/YYYY hh:mm:ss')),1,0) as Test

from ...

then you expression will be

Count({$ <Test={1}>} ID)

Regards,

Kaushik Solanki

Re: Set analysis count

Wed, 20 Mar 2013 09:52:16 GMT

TABLE1: Calendar controlled by DATE1

ID DATE1

d123 01/01/2013 00:00:00

d456 01/01/2013 00:00:00

d789 15/05/2013 00:00:00

d578 21/08/2013 00:00:00

d798 07/07/2013 00:00:00

d487 15/09/2013 00:00:00

TABLE2:

DATE2 DATE3

01/01/2013 00:00:00 05/08/2013 00:00:00

01/01/2013 00:00:00 01/01/2013 00:00:00

10/06/2013 00:00:00 18/07/2013 00:00:00

07/07/2013 00:00:00 15/05/2013 00:00:00

07/07/2013 00:00:00 21/08/2013 00:00:00

20/09/2013 00:00:00 15/09/2013 00:00:00

I am trying to match DATE1=DATE2 and count the ID. Because the calendar is controlled by DATE1 the count doesnt change. But when I select date from DATE2 the count is correct.

I hope I put the scenario correctly.

Re: Set analysis count

kaushiknsolanki — Wed, 20 Mar 2013 10:01:36 GMT

HI,

Try this.

Count({<Date1 = p(Date2)>}ID)

Regards,

Kaushik Solanki

Re: Set analysis count

Wed, 20 Mar 2013 10:17:15 GMT

I have a chart with DATE1 as dimension as it controls the calendar. The count works but only when I select dates from DATE2 not DATE1. How can DATE2 be controlled with DATE1. Cant join them. The app is huge and it affects the other tabs.

Re: Set analysis count

Anonymous — Wed, 20 Mar 2013 10:21:13 GMT

Hello,

please assocotea all Dates

Date(yourdate,'DD/MM/YYYY') AS DATE

DO this in all tables

now use Year , month, Day field as list box , like this you can control all dates.

if possible craete a master calendar.

Regards,

anant

Re: Set analysis count

kaushiknsolanki — Wed, 20 Mar 2013 10:24:37 GMT

Hi,

Is it possible for you to upload the sample QVW file.

Regards,

Kaushik Solanki

Re: Set analysis count

Wed, 20 Mar 2013 10:25:31 GMT

Yes Sir. master calendar is created by using DATE1 field. Because it is controlled by DATE1 field and I need the count when DATE1 = DATE2 is selected it counts the total number rather than for that specific date. As explained above. When i pull DATE2 to a list box and specific date the count is right.

Re: Set analysis count

kaushiknsolanki — Wed, 20 Mar 2013 10:32:12 GMT

Hi,

Find the attachment for clarification.

Regards,

Kaushik Solanki

Re: Set analysis count

Wed, 20 Mar 2013 10:59:16 GMT

Please find attached.

Re: Set analysis count

kaushiknsolanki — Wed, 20 Mar 2013 11:09:59 GMT

Hi,

By joining tables you can do this easily.

Have a look at the attachment.

Regards,

Kaushik Solanki

Re: Set analysis count

Wed, 20 Mar 2013 11:13:56 GMT

In the qvw I haave attached. When May 1st 2013 is selected the bar chart gives value as 4 but the line chart returns 41. But when 01/05/2013 is selected in DATE2 field it returns the value 8 which is correct.

Ideally I am looking the chart to display 4 in bar and 8 in line when May 1st 2013 is selected in the calendar.

Re: Set analysis count

kaushiknsolanki — Wed, 20 Mar 2013 11:15:51 GMT

Hi,

Have a look at the example attached in my previous post. It does the same as what you want.

Regards,

Kaushik Solanki