topic counts in QlikView

counts

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

HI All,

PLease find the attahcment of the QVW file and Excel file.

I have a bar chart which will show the values of created and closed tickets each month respectively ...

count of created is showing properly but Count of Closed is adding up everything...

i need to chow the count of closed for the selected month only which should be like , if the entry is missing in closed date the it should consider the count of resolved that for that perticular month

only.

For example:

slelect Level = Low For Feb-2017 open is giving the correct value which is 59 ... and close value is showing wrong as 40 , which should be 16 that is sum of closed and resolved tickets only for Feb-2017 (selected month)

thanks in advance

Re: counts

MK_QSL — Sat, 29 Jul 2017 16:34:24 GMT

How total count for Feb 2017 (Level = Low) = 16?

Can you provide the list of all those tickets in excel?

Re: counts

passionate — Sat, 29 Jul 2017 16:59:37 GMT

Hi John,

Is the requirement

For:

Month: Feb 2017 and Level: low

open: 59 and closed: 23

What does Closed specify? Please give business scenario.

As Closed =16 requirement is not clear.

Regards,

Pankaj

Re: counts

smilingjohn — Sat, 29 Jul 2017 17:04:11 GMT

Hi Manish

Thanks for the reply ,,,

There are created and closed tickets ,,,

When we select Level=Low ( or any other) and select jan ninth from created date then the count of Closed should be shown only for slected month ......and if there are no entry for that month in closed then it should consider Resolved Date and give the counts for closed tickets...

in excel fileter If you see the count for Closed should be 19 for Feb when Level=low and created will be 59 ...

and in my bar chart it shows correct Create counts but count of Closed counts everything ..

Please suggest

Thanks

Re: counts

smilingjohn — Sat, 29 Jul 2017 17:09:10 GMT

Hi Pankaj ...

What iam trying to achive is that ...

for selected date the the bar chart will display count of created and count of closed .......( count of close should be for selcted month ..if i slect say example Jan 2017 then close count should display count of close for created month section )...

I hope i was able to make you understand ...

Thanks

Re: counts

MK_QSL — Sat, 29 Jul 2017 17:10:19 GMT

Your reply is creating more confusion.

select jan ninth from created date then the count of Closed should be shown only for slected month

You are selecting a date and you want closed ticket count for that whole month !

Kindly make it clear.

Re: counts

smilingjohn — Sat, 29 Jul 2017 17:36:43 GMT

HI manish ,

HI manish will slect oly MonthYear ....thats why have converted the Dates as MOnthName...

Re: counts

ziadm — Sat, 29 Jul 2017 17:41:05 GMT

Try this .. this should do exactly what you are trying to achieve

Canonical Date

Re: counts

ziadm — Sat, 29 Jul 2017 17:49:46 GMT

Or Check this may good enough

Data:

LOAD Level,

Created,

Resolved,

Closed

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

FACT:

load MonthName(Created)&Level as KeyID,

MonthName(Created) as MonthYear,

Level,

count(Created) as TicketsCreated

Resident Data

group by MonthName(Created) ,Level;

Left Join

load MonthName(Resolved)&Level as KeyID,

//MonthName(Resolved) as MonthYear,

Level,

count(Resolved) as TicketsResolved

Resident Data

group by MonthName(Resolved) ,Level;

Left Join

load MonthName(Closed)&Level as KeyID,

//MonthName(Resolved) as MonthYear,

Level,

count(Closed) as TicketsClosed

Resident Data

group by MonthName(Closed) ,Level;

drop Table Data;

Re: counts

MK_QSL — Sat, 29 Jul 2017 17:59:01 GMT

Give us one proper example. What you want to select and what you want as output.

Looks like you are in hurry while giving answer.

Take your time while giving the explanation.

Sometimes, you are giving example of Jan 9th and then in next line Feb 19th.

Hope you understand.

Re: counts

antoniotiman — Sat, 29 Jul 2017 18:04:40 GMT

John,

40 is correct

28 Count(Closed) + 12 IsNull(Closed) with Resolved

Regards,

Antonio