Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

yarimarchetti
New Contributor

Monthly sum of last available date

Hello,

I got data that look like this:

DATECOUNTRYCUSTOMER BASE DAILY
2018-01-30US0
2018-01-30AU1
2018-01-31US1
2018-01-31AU1
2018-02-27US5
2018-02-27AU1
2018-02-28US5
2018-02-28AU1
2018-03-29US6
2018-03-29AU1
2018-03-30US7
2018-03-30AU2
2018-04-13US7
2018-04-13AU3
2018-04-15US8
2018-04-15AU3

and I'd like to create a table where the dimension is the month and measure is the sum of the customer base column of the most recent date of that month: for the past month it, usually, coincides with the last day of the month but for the current month it must show the most recent available values.

So the table above should generate a table like this one:

MONTHCUSTOMBER BASE
2018-012
2018-026
2018-039
2018-0411

Could you help me with this?

Thanks,

Yari

Tags (1)
4 Replies
thiago_justen
Valued Contributor III

Re: Monthly sum of last available date

Do you have a field called Month (mm format)?

Considering a negative answer I'd suggest you:

As dimension:

Date (DATE,'YYYY-MM')

As expression:

Sum(Aggr(If(DATE=Max(Total DATE),[CUSTOMER BASE]),Date (DATE,'YYYY-MM')))

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
yarimarchetti
New Contributor

Re: Monthly sum of last available date

Thanks Thiago for your answer, but unfortunately it doesn't work.


To give you some more info: I do have a [DATE.autoCalendar.YearMonth] field, which it's the one I'm using as dimension. I tried using the formula you provided "as is" and also changing Date (DATE,'YYYY-MM') to use [DATE.autoCalendar.YearMonth] but with no results: in both cases I get all values equal to 0.


Yari

thiago_justen
Valued Contributor III

Re: Monthly sum of last available date

Are you able to share an example app? 

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
yarimarchetti
New Contributor

Re: Monthly sum of last available date

Please find the test app attached: I included both widgets using the solution you proposed and another one using the autocalendar YearMonth field.

Best Regards,

Yari

Community Browser