Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ruma_barman
New Contributor II

Set Analysis

Hi Team,

I want to get the count of population of last day of the year for corresponding year in pivot table(App is attached).

How to do this?

Regards,

Ruma

1 Solution

Accepted Solutions

Re: Set Analysis

Try this

Count({<Date = {"=Date = YearStart(Date, 1)-1"}>}POPULATIONCNT)

Capture.PNG

4 Replies

Re: Set Analysis

Try this

Count({<Date = {"=Date = YearStart(Date, 1)-1"}>}POPULATIONCNT)

Capture.PNG

jubarros
Contributor II

Re: Set Analysis

Hi:

I prefer calculated this metrics in the script. I create a Flag that I use to count:

//Your table

Fact_t:

LOAD

    "Count",

    POPULATIONCNT,

    "Date",

    Year(Date)             as Year,

    Month(Date)         as Month

FROM [lib://Input/File.xlsx]

(ooxml, embedded labels, table is Sheet1);

// Select max Date

MaxDay;

LOAD

    MaxDate,

    1             as FlagMaxDate;

LOAD

    Year,

    Month,

    Max("Date")    as MaxDate

Resident Fact_t

Group by Year, Month;

//Add a Flag to count maxDate

Fact:

Load * Resident Fact_t;

left join(Fact)

Load * Resident MaxDay;

Drop tables Fact_t, MaxDay;

Best Regards,

Juan P. Barroso

ruma_barman
New Contributor II

Re: Set Analysis

Hi Sunny,

To get the count of previous year for corresponding year.How to achieve that?

Re: Set Analysis

Use Above() function

Above(Count({<Date = {"=Date = YearStart(Date, 1)-1"}>}POPULATIONCNT))

or

Above(TOTAL Count({<Date = {"=Date = YearStart(Date, 1)-1"}>}POPULATIONCNT))

Community Browser