Pivot Showing Greater Number When Unfiltered

[Anonymous]

I have this data set where I want to count accounts that had more than one device to them. When the chart is unfiltered I get a higher number, when I filter it to the month it should the correct answer. Can someone help please.

SCRIPT:

LOAD

    MonthYear,

    Account,

    Device,

    LOG_DATE

FROM [lib://AttachedFiles/Qlik Community.xlsx]

(ooxml, embedded labels, table is Sheet1);

DIMENSION:

MonthYear

EXPRESSION:

aggr(count({< Account = {"=Count(distinct Device) > 1"} >} DISTINCT Account), MonthYear)

Chart Shows this:

When I filter to individual month i get the right answer.

Any ideas?

I want the calculation to be done on the first end (in pivot table)

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT Aggr(If(Count(DISTINCT Device) > 1, Account), Account, MonthYear))

[Anonymous]

Worked like a charm! Thank you so much!