topic Count help? in QlikView

Count help?

amit_saini — Fri, 02 Mar 2018 09:54:47 GMT

Dear All,

I'm having below case:

One line cab belong to different stations , in above case it is 3.

So what I'm looking for is count of "Global Station Label" but need to include line count too.

As of now I'm using below expression:

=count(DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Technology-={'No Fuel BM','No SCR inj'}>}Global Station Label)

How should I include here count of line???

I'm not looking for simply * above expression with count(Line) , I need somehow "And" condition to consider both factors.

Please suggest!

Regards,
AS

Re: Count help?

Chanty4u — Fri, 02 Mar 2018 09:56:15 GMT

you can create a flag and add it in set expression? did you tried this?

Re: Count help?

vishsaggi — Fri, 02 Mar 2018 10:00:34 GMT

‌mMay be try like

Count(Distinct Total <Line> GlobalStationLabel)

Re: Count help?

sunny_talwar — Fri, 02 Mar 2018 10:00:39 GMT

May be this

=Count(DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Technology-={'No Fuel BM','No SCR inj'}>} [Global Station Label]&Line)

Re: Count help?

amit_saini — Mon, 05 Mar 2018 12:13:54 GMT

Hi Sunny,

When I'm Dimension as Plant and Expression =

count(DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Technology-={'No Fuel BM','No SCR inj'}>}Station_Label & Line_Name)*24

This is giving me Output as "288" for week number 6. See below:

But when I'm using same expression for keeping Week as Dimm , it's showing me different output -216

Not sure why , I should get same 0/p on Weekly chart.

Any idea?

Regards,

Re: Count help?

sunny_talwar — Mon, 05 Mar 2018 12:18:56 GMT

Not sure, but could be something related to DISTINCT. Remove DISTINCT and see if you still see different numbers.

Re: Count help?

amit_saini — Mon, 05 Mar 2018 12:51:29 GMT

sorry without distinct it's showing very big numbers.

Thanks,

Re: Count help?

sunny_talwar — Mon, 05 Mar 2018 12:55:28 GMT

Don't have to be sorry for anything .

I know it shows big number, but my point was to check if the numbers are the same when you don't use DISTINCT. If this is true, you will need to use Sum(Aggr()) function to fix this

Re: Count help?

amit_saini — Mon, 05 Mar 2018 13:04:42 GMT

No even without distinct numbers are not same of charts.

Regards,

Re: Count help?

sunny_talwar — Mon, 05 Mar 2018 13:05:35 GMT

Would you be able to share a sample to check this out?

Re: Count help?

amit_saini — Mon, 05 Mar 2018 13:20:08 GMT

Sorry Sunny not possible

Regards,

Re: Count help?

sunny_talwar — Mon, 05 Mar 2018 13:21:32 GMT

Don't know any other way to help.... may be someone else can help

Re: Count help?

amit_saini — Mon, 05 Mar 2018 13:24:12 GMT

anyways thanks a lot for trying ur best as always

Truly appreciated!

Regards,

Re: Count help?

sunny_talwar — Mon, 05 Mar 2018 13:26:55 GMT

No problem at all Amit

Re: Count help?

amit_saini — Mon, 05 Mar 2018 13:53:39 GMT

Luckily got some sample data.

PFA!

Regards,

Re: Count help?

amit_saini — Mon, 05 Mar 2018 13:55:13 GMT

In general i should be able to see 2016 on week 6 in line chart and not 1176.

Regards,

Re: Count help?

sunny_talwar — Mon, 05 Mar 2018 14:13:11 GMT

Try this

=Sum({<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Line_Name=>}Aggr(

Count(DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=,Line_Name=>}Day)*

Count(TOTAL <Facility_Id> DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Standard_Name=>}Station_Label & Line_Name)*24, Facility_Id, Week))

Re: Count help?

amit_saini — Mon, 05 Mar 2018 14:18:30 GMT

No Sunny not like this:

Different Week values will keep changing on bar charts and same values it should show in line for respective week.

Example for Week 5 its 864 , but in line chart it shows 2016 which is wrong.

Thanks,

Re: Count help?

sunny_talwar — Mon, 05 Mar 2018 14:23:04 GMT

I don't really know what you need... but you were ignoring selection in Month field and in your image you have selected Feb... which is still impacting your top chart, but not the bottom chart.... For me, right now, it is just a guessing game... you will need to elaborate on what you need

Re: Count help?

amit_saini — Mon, 05 Mar 2018 14:29:36 GMT

sorry forgot selection on Month.

Thanks,