topic Re: Logic in Pivot Table in QlikView

Logic in Pivot Table

aveeeeeee7en — Tue, 25 Mar 2014 20:05:25 GMT

Hi Community

See the Attachment

Regards

Aviral Nag

Re: Logic in Pivot Table

Tue, 25 Mar 2014 20:19:55 GMT

Maybe you can use this:

Count(DISTINCT if(aggr(nodistinct max(CompletedDate),MaxState)=CompletedDate,TicketID))

Attached.

Re: Logic in Pivot Table

aveeeeeee7en — Tue, 25 Mar 2014 20:57:24 GMT

Thanks Ajay for your reply but your logic is not working.

If you select Feb 2014 from field Monthname and Select India from field State it will show you 6 Tickets whereas from your formula i am getting 1 Ticket.

Re: Logic in Pivot Table

Tue, 25 Mar 2014 21:21:31 GMT

For the selections you mentioned the max date in Completed Date is 27/02/2014 for which there is just one

TicketID - 110.

Should a different logic be used to calculate that.

Re: Logic in Pivot Table

aveeeeeee7en — Wed, 26 Mar 2014 01:30:28 GMT

If You Go to Edit Script, than you'll see that i have picked the 2nd Max Completed Data "Key" wise.

So, the Max Date will come different for each case.

Re: Logic in Pivot Table

aveeeeeee7en — Wed, 26 Mar 2014 07:06:43 GMT

Any Suggestions.

Re: Logic in Pivot Table

PrashantSangle — Wed, 26 Mar 2014 07:12:11 GMT

Hi,

Use this as your expression

Aggr(Count(DISTINCT TicketID),TicketID)

Checked enclose file,

Re: Logic in Pivot Table

aveeeeeee7en — Wed, 26 Mar 2014 07:46:12 GMT

Thanks for your reply.

This is not what I want.. Kindly read the post and see the Attachment.

Re: Logic in Pivot Table

tresB — Wed, 26 Mar 2014 09:01:02 GMT

Try :

Count({<State=p(MaxState)>}DISTINCT TicketID)

Re: Re: Logic in Pivot Table

aveeeeeee7en — Wed, 26 Mar 2014 09:56:36 GMT

Thanks Tresesco for your reply.

There were some minute discrepancies. Not able to understand how to tackle it.

See the Attchment.

Re: Logic in Pivot Table

adiarnon — Wed, 26 Mar 2014 10:10:06 GMT

HI,

I didn't get why in your dimension there is maxstate?!

why don't you change it to state?

Re: Logic in Pivot Table

tresB — Wed, 26 Mar 2014 10:18:00 GMT

Not very clear about your requirement. Please explain expected output for at least for two scenarios, like: with one or two particular selection(s) and one wihout any selection. If possible explain the logic too.

Re: Re: Logic in Pivot Table

aveeeeeee7en — Wed, 26 Mar 2014 11:46:49 GMT

Sir

I have one Main Table which has all Closed Tickets.

TicketID,

Location,

Name,

Month,

Year,

Key

From this Table I am calculating Repeat Calls by doing Left Join on it as done in Attached Qvw.

What I want :

To show Count of Key along with count of Distinct TicketId and show both of them Repeat Calls Location wise.

Scenario1: When click on MaxMonthName - Feb 2014 the Count of Repeat Calls Filter out and show India - 1 and Japan - 1

whereas data from Main Table - Count of Closed Tickets is:

India - 6 Japan - 3 when click on Monthname