Re: Logic in Pivot Table - Qlik Community

aveeeeeee7en · ‎2014-03-25

Hi Community

See the Attachment

Regards

Aviral Nag

Report Inappropriate Content · ‎2014-03-25

Maybe you can use this:

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

Attached.

aveeeeeee7en · ‎2014-03-25

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.

Report Inappropriate Content · ‎2014-03-25

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.

aveeeeeee7en · ‎2014-03-25

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.

aveeeeeee7en · ‎2014-03-26

Any Suggestions.

PrashantSangle · ‎2014-03-26

Hi,

Use this as your expression

Aggr(Count(DISTINCT TicketID),TicketID)

Checked enclose file,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

aveeeeeee7en · ‎2014-03-26

Thanks for your reply.

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

tresesco · ‎2014-03-26

Try :

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

aveeeeeee7en · ‎2014-03-26

Thanks Tresesco for your reply.

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

See the Attchment.