Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ruma_barman
Creator
Creator

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
sunny_talwar

Try this

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

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

Try this

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

Capture.PNG

jubarrosor
Partner Ambassador
Partner Ambassador

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
Creator
Creator
Author

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))