12 Replies Latest reply: Mar 26, 2014 7:46 AM by AVIRAL NAG

# Logic in Pivot Table

Hi Community

See the Attachment

Regards

Aviral Nag

• ###### Re: Logic in Pivot Table

Maybe you can use this:

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

Attached.

• ###### Re: Logic in Pivot Table

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

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

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

• Any Suggestions.
• ###### Re: Logic in Pivot Table

Hi,

Aggr(Count(DISTINCT TicketID),TicketID)

Checked enclose file,

• ###### Re: Logic in Pivot Table

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

• ###### Re: Logic in Pivot Table

Try :

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

• ###### Re: Re: Logic in Pivot Table

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

See the Attchment.

• ###### Re: Logic in Pivot Table

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

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

• ###### Re: Logic in Pivot Table

HI,

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

why don't you change it to state?