Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

afrozahyder
New Contributor

Pivot Showing Greater Number When Unfiltered

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:

WrongAnswer.png

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

RightAnswer.png

Any ideas?

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

1 Solution

Accepted Solutions

Re: Pivot Showing Greater Number When Unfiltered

Try this

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

2 Replies

Re: Pivot Showing Greater Number When Unfiltered

Try this

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

afrozahyder
New Contributor

Re: Pivot Showing Greater Number When Unfiltered

Worked like a charm! Thank you so much!

Community Browser