Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Help needed: Aggregating Product/Sales Data (Min/Max Dates)

Hi,

We have data in the format below (but over hundreds of thousands of rows):

      

Opportunity_IDOpportunity_NameOpportunity_AmountProduct_NameProduct_AmountRevenue Start DateRevenue Close Date
1Opportunity 1350000Product 12500019/12/201604/05/2017
1Opportunity 1350000Product 23500019/12/201604/05/2017
1Opportunity 1350000Product 34000024/12/201604/05/2017
1Opportunity 1350000Product 41500019/12/201604/05/2017
1Opportunity 1350000Product 56500019/12/201604/05/2017
1Opportunity 1350000Product 68000019/12/201604/05/2017
1Opportunity 1350000Product 75000019/12/201604/05/2017
1Opportunity 1350000Product 84000019/12/201604/05/2017

To note,

  • There is not a consistent start date across all products in an opportunity
    • The same with close dates

The requirement is to output the data in the format:

    

Opportunity_NameOpportunity_AmountRevenue Start DateRevenue Close Date
Opportunity 135000019/12/2016

04/05/2017

Here we are showing the opportunity data at an aggregated level, and showing the earliest corresponding revenue start and close date.

Attempting to replicate that in Qlik Sense produces the following:

Min Dates.png

I don't want to see opportunity 1 appear twice, but just once with the earliest revenue start date. Seeing it twice also means we double count in the totals.

What is the best way of achieving the required output?

Please get back to me with any questions and thanks in advance!

Qlik User

Tags (3)
2 Replies

Re: Help needed: Aggregating Product/Sales Data (Min/Max Dates)

Use Revenue Start and Close Dates as Measure instead of dimension

Date(Min([Revenue Start Date]))

Date(Max([Revenue Close Date]))

Not applicable

Re: Help needed: Aggregating Product/Sales Data (Min/Max Dates)

Thanks Sunny - I realised that there was a flag on one of the line items for each opportunity indicating the "opportunity start/close date", so I didn't actually need to implement such a condition.

A separate/unrelated question I was wondering if you can help with..

I want to show the top 10 highest revenue deals, with the rest being shown as "other" - How would I go about doing this in a table?