counts

[smilingjohn]

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

[MK_QSL]

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

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

[passionate]

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]

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]

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]

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]

HI manish ,

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

[ziadm]

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

Canonical Date

[ziadm]

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]

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.