Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Specialist
Specialist

counts

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

10 Replies
MK_QSL
MVP
MVP

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

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

passionate
Specialist
Specialist

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 

smilingjohn
Specialist
Specialist
Author

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

smilingjohn
Specialist
Specialist
Author

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

MK_QSL
MVP
MVP

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.

smilingjohn
Specialist
Specialist
Author

HI manish ,

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

ziadm
Specialist
Specialist

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

Canonical Date

ziadm
Specialist
Specialist

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;

MK_QSL
MVP
MVP

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.