topic Re: How to calculate current vs previous month data, based on month selection in App Development

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

abc_18 — Tue, 21 Dec 2021 21:30:18 GMT

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

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

Kashyap_R — Tue, 21 Jan 2020 10:33:07 GMT

try this

Current

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

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

Thanks

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

abc_18 — Tue, 21 Jan 2020 10:55:18 GMT

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

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

Kashyap_R — Tue, 21 Jan 2020 11:02:29 GMT

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

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

abc_18 — Tue, 21 Jan 2020 12:45:18 GMT

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

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

Prashant_Naik — Tue, 21 Jan 2020 14:27:33 GMT

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

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

Kashyap_R — Tue, 21 Jan 2020 15:05:13 GMT

Please o through the attached QVF it's working

Thanks

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

Prashant_Naik — Tue, 21 Jan 2020 14:57:10 GMT

Hi kashyap,

I am unable to find any attachment.

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

Kashyap_R — Tue, 21 Jan 2020 15:07:15 GMT

I have attached

Thanks

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

abc_18 — Wed, 22 Jan 2020 06:26:12 GMT

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

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

Kashyap_R — Wed, 22 Jan 2020 06:31:07 GMT

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)

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

Hope this helps

Thanks

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

abc_18 — Wed, 22 Jan 2020 09:03:51 GMT

Hi kashyap,

Thanks for your response.

I have one question, requirement is that YTD calculation should based on Financial year selection, in my script cycle is from April to march.

For that I have already set the SET FirstMonthOfYear=4; in the script.

Now, I have data available only for 2019 (Jun-Dec) and 2020 (Only Jan), so if user selects FY 2020 (FY 2020-FY 2021), then for current YTD it should show '0' as I have only Jan 2020 data is available.

but in my case, even though I select 2020 for current YTD it showing some values.

Can you please let me know if any changes in expression is required.

Regards

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

Kashyap_R — Wed, 22 Jan 2020 09:21:31 GMT

Kindly go through this

https://community.qlik.com/t5/New-to-QlikView/Fiscal-calendar/m-p/613708

Thanks

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

abc_18 — Thu, 23 Jan 2020 05:39:06 GMT

Hi Kashyap,

Thank you very much for your help, I have implemented same and it's working as expected.

Regards

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

IronSight7 — Sun, 14 Jun 2020 09:26:06 GMT

hello,

Kindly help me too, please?

I just need to manually put this on the KPI, but I am getting the error. I want to manually define the Month and the Year like this.

First KPI

=Sum({Month={10},Year = {2019}} [Gross Sales])

Second KPI

=Sum({Month={9},Year = {2019}} [Gross Sales])

I tried writing like this - but it's not working.

Sum({<SalesDate = {'$(=Max(SalesDate))'}>}[Gross Sales])

Sum({<SalesDate = {'$(=AddMonths(Max(SalesDate),-1))'}>}[Gross Sales])

Thanks in advance for the help.

Regards,

Iron

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

Prashant_Naik — Sun, 14 Jun 2020 09:43:57 GMT

Hi,

Just put the below expressions-

First KPI

=Sum({<Month={'10'},Year = {'2019'}>} [Gross Sales])

Second KPI

=Sum({<Month={'9'},Year = {'2019'}>} [Gross Sales])

I tried writing like this - but it's not working.

Sum({<SalesDate = {"$(=Max(SalesDate))"}>}[Gross Sales])

Sum({<SalesDate = {"$(=AddMonths(Max(SalesDate),-1))"}>}[Gross Sales])

In your expression I just put single and double quotes nothing more.

Regards,

Prashant

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

IronSight7 — Sun, 14 Jun 2020 10:27:24 GMT

hello Prashant, thank you so much!!! it's working!!!