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

[abc_18]

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

[Kashyap_R · Accepted solution]

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

[abc_18]

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

[Kashyap_R]

Hi

Kindly go through this

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

Thanks

[abc_18]

Hi Kashyap,

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

Regards

[IronSight7]

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

[Prashant_Naik]

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

[IronSight7]

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