Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

anellibridge
New Contributor II

Count Objects for Max Date in Set Analysis

Hello,

I have a table showing Category Code, Sales Date, and Objects:

TESTA.JPG


I want to calculate for each Category Code the quantity of distinct Objects that have the max Sales Date for each Category Code:
TESTB.JPG

In this example for the Category Code=1, I want that #Objects=2 because there are 2 distinct Objects that have the Sales Date 18/12/2017.

I'm using the Version November 2017 of Qlik Sense.

Any suggestion?

thx a lot,

Arianna

1 Solution

Accepted Solutions
anellibridge
New Contributor II

Re: Count Objects for Max Date in Set Analysis

Hi Hector,

thank u a lot for your reply, but it doesn't function that formula.

After a lot of test I found the solution!

Count( Distinct If(Aggr(NODISTINCT Max( TOTAL <[Category Code]> [Sales Date]),[Category Code],[Object]) = [Sales Date], [Object]))


Thanks a lot to everyone,

11 Replies
galax_allu
Valued Contributor

Re: Count Objects for Max Date in Set Analysis

Count(Aggr(Only(Objects), [Sales Date]))

Re: Count Objects for Max Date in Set Analysis

May be try this

Dimension

Category Code

Measure

Max([Sale Date])

Sum(Aggr(If([Sale Date] = Max(TOTAL <[Category Code]> [Sales Date]), 1, 0), [Category Code], [Sale Date]))

hjimenez25
New Contributor II

Re: Count Objects for Max Date in Set Analysis

You should try this:

In your Data Load Editor:

Sales:

LOAD

    "Category Code",

    "Sales Date",

    Object

FROM [lib://ExcelFilesForTest/Example1.xlsx]

(ooxml, embedded labels, table is Hoja1);

left Join (Sales)

LOAD

    "Category Code",

    date(Max("Sales Date")) as MaxDatePerCategory

FROM [lib://ExcelFilesForTest/Example1.xlsx]

(ooxml, embedded labels, table is Hoja1) group by "Category Code";

SalesFinal:

LOAD *,

     if("Sales Date" = MaxDatePerCategory,1,0) as _flagLastDate

Resident Sales;

drop Table Sales;

Then use this in your Table:

Use Category Code as Dimension, and count({$<_flagLastDate={1}>} distinct Object)  as mease.

I hope this helps.

anellibridge
New Contributor II

Re: Count Objects for Max Date in Set Analysis

Hi Hector,

thank u for your reply, but I can't change the Data Model  (it's more complex than this).

I need a formula that allows the dynamic selection of the Dates or something else that change the quantity of Objects

thank u so much,

anellibridge
New Contributor II

Re: Count Objects for Max Date in Set Analysis

Hi Allu,

Thank u for your reply, but unfortunately it doesn't resolve my problem.

I need a formula that calculates for each Category Code how many distinct Objects have the Max Sales Date exists.

thank u so much

anellibridge
New Contributor II

Re: Count Objects for Max Date in Set Analysis

Hi Sunny,

thank u so much for your reply!

I tested your formula and it works as I expected. In particular that it's ok if I use it in the first Table, because I obtain 1 in the rows where I have the Max Date.

My problem is calculate the count of this 1 in the second Table.

Could you give me another suggestion for this?

thank u a lot

Re: Count Objects for Max Date in Set Analysis

Not entirely sure what you mean? Isn't the 2nd table the expected output of what you are looking to get?

anellibridge
New Contributor II

Re: Count Objects for Max Date in Set Analysis

Yes!

I modified your formula and It seems to work . Now I'm doing some test with this:


Sum(TOTAL <[Category Code]> if([Sales Date] = Aggr(max(TOTAL <[Category Code]> [Sales Date]), [Category Code], [Object]), 1, 0))

hjimenez25
New Contributor II

Re: Count Objects for Max Date in Set Analysis

Hi Arianna,

I know you can not modify your data model, it could be better. This is a modified version of Sunny´s solution and yours:

Try this:

  sum(TOTAL <[Category Code]> aggr(if([Sales Date] = max(TOTAL <[Category Code]> [Sales Date]), 1, 0),[Category Code], [Sales Date],[Object]))

I hope this help you.

Community Browser