Qlik Community

Qlik Sense App Development

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

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

How to calculate current vs previous month data, based on month selection

Hello everyone,

In my dashboard, I have two KPI's  which shows data for current month and previous month.

I have to show the sales data based on month selection for entire month till date.

Suppose users inputs month as 'Jan' then for current month it should show 1st Jan - 21st Jan till today and in previous month KPI it should show 1st Dec - 21st Dec 2019 data.

 

Please suggest how can I achieve this.

 

Thanks

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: How to calculate current vs previous month data, based on month selection

Hi

Try 

Current Year YTD

=Sum({<sap_invoice_date={">=$(=YearStart(max(sap_invoice_date))<=$(=Date(Max(sap_invoice_date)))"},Month=,Year=>}invoice_quantity)

Previous Year YTD

Sum({<sap_invoice_date={">=$(=Date(MonthStart(addmonths(Max(sap_invoice_date),-1))))<=$(=Date(AddYear(Max(sap_invoice_date),-1)))"},Month,Year>} invoice_quantity)

or 

You can create a variable and you can use them in your expression

Hope this helps

Thanks

View solution in original post

16 Replies
Highlighted
Partner
Partner

Re: How to calculate current vs previous month data, based on month selection

hi

try this

Current

Sum({<[Date]={">=$(=MonthStart(max([Date])))<=$(=Date(Max([Date])))"}>}Value)

Previous

Sum({<[Date]={">=$(=MonthStart(Addmonths(max([Date]),-1)))<=$(=(Addmonths(max([Date]),-1)))"}>}Value)

Thanks

Highlighted
Creator II
Creator II

Re: How to calculate current vs previous month data, based on month selection

Hi kashyap,

 

Thanks for your quick response.

 

I tried to using this formula, but if I use  >=, it's just giving me '0' value, what can be the reason.

Sum({<[Date]={">=$(=MonthStart(max([Date])))<=$(=Date(Max([Date])))"}>}Value)

 

I have checked the data and for Jan till 19, we have data available, still it giving '0' value.

 

Thanks

Highlighted
Partner
Partner

Re: How to calculate current vs previous month data, based on month selection

Hi

If it is possible can u share the QVF or some sample, So that I can know what's going on

Also

Check what are the filters you have applied if there are any other filters like year or date try nullifying those

Check the Date format too

Thanks

Highlighted
Creator II
Creator II

Re: How to calculate current vs previous month data, based on month selection

Hi kashyap,

 

I have changed the date format, now for current month data is showing, but still previous month data is showing '0'.

This is the formula I am using:-

=Sum({<sap_invoice_date={">=$(=MonthStart(addmonths(Max(sap_invoice_date),-1))) <=$(=AddMonths(Max(sap_invoice_date),-1))"}>} invoice_quantity)

 

I have also attached the sample test data for reference.

Regards

Highlighted
Partner
Partner

Re: How to calculate current vs previous month data, based on month selection

Hi,

May be this

Sum({<sap_invoice_date={">=$(=Date(MonthStart(addmonths(Max(sap_invoice_date),-1))),'YYYY-MM-DD' )<=$(=Date(AddMonths(Max(sap_invoice_date),-1)),'YYYY-MM-DD')"}>} invoice_quantity)

Regards,

Prashant

Highlighted
Partner
Partner

Re: How to calculate current vs previous month data, based on month selection

Hi

Please o through the attached QVF it's working

Thanks

Highlighted
Partner
Partner

Re: How to calculate current vs previous month data, based on month selection

Hi kashyap,

I am unable to find any attachment.

 

Highlighted
Partner
Partner

Re: How to calculate current vs previous month data, based on month selection

Hi

I have attached

Thanks

Highlighted
Creator II
Creator II

Re: How to calculate current vs previous month data, based on month selection

Hi Kashyap,

Thank you very much, you just saved my day.

Can I ask for more help, I need to calculate Current YTD  vs previous YTD (Yeartodate).

for ex  if in 2020 there is only one month January with data till today , it will be compare to Jan month of 2019 from 1st Jan till 21st .

and if there are three months in 2019 it will compare to same three months of 2018.

Please suggest.

 

Reagrds