Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ugurarslan
New Contributor

Date expression current month, prior month, prior quarter

Dear,

I have date dimension which is as following:

ProdDate         ProdTotal    

12/1/2017        4434

1/1/2018          3234

2/1/2018          4352

3/1/2018          5433

4/1/2018          4456

I have 4 KPI Chart which need to update automatically with the new number, at this moment I use the following expressions which I need to update manually every month.

How can I automate this that it updates every month with the new data? (add months)

Current Month ProdTotal    Sum({<[ProdDate]={'3/1/2018'}>}[ProdTotal])

Prior Month ProdTotal        Sum({<[ProdDate]={'2/1/2018'}>}[ProdTotal])

Prior Quarter ProdTotal      Sum({<[ProdDate]={'11/1/2018'}>}[ProdTotal])

Prior Year ProdTotal           Sum({<[ProdDate]={'3/1/2017'}>}[ProdTotal])

Thanks

Ugur

4 Replies
MVP
MVP

Re: Date expression current month, prior month, prior quarter

What is the logic behind these dates?

ugurarslan
New Contributor

Re: Date expression current month, prior month, prior quarter

Hi Manish, it is an excel file which a date field and a measurement, very simple.

MVP
MVP

Re: Date expression current month, prior month, prior quarter

It's not simple...!!

1) You haven't mentioned how you are getting dates?

2) to update automatically with the new number? Which new numbers?

Current Month ProdTotal    Sum({<[ProdDate]={'3/1/2018'}>}[ProdTotal])

Prior Month ProdTotal        Sum({<[ProdDate]={'2/1/2018'}>}[ProdTotal])

Prior Quarter ProdTotal      Sum({<[ProdDate]={'11/1/2018'}>}[ProdTotal])

Prior Year ProdTotal           Sum({<[ProdDate]={'3/1/2017'}>}[ProdTotal])


Also, not understood, why you have used those dates here? For 3rd expression, you have 11/1/2018 which is not even in sample data..

OmarBenSalem
Esteemed Contributor

Re: Date expression current month, prior month, prior quarter

Imagine you have this measure:

sum(Measure)

1) Current Month:

sum({<Date={"$(=max(Date))"}>}Measure)

2) previous Month :

sum({<Date={"$(=Addmonths(max(Date),-1))"}>}Measure)


3)Previous Year:

sum({<Date={"$(=AddYears(max(Date),-1))"}>}Measure)

Community Browser