Set Analysis

[ruma_barman]

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

[sunny_talwar · Accepted solution]

Try this

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

[jubarrosor]

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]

Hi Sunny,

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

[sunny_talwar]

Use Above() function

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

or

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