Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
Hi
Please o through the attached QVF it's working
Thanks
Hi kashyap,
I am unable to find any attachment.
Hi
I have attached
Thanks
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