Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have requirement in my project that if we are in the current quarter then display the sum of sales for current month in the current querter. Else display the sum of sales for the latest month in that quarter.
For example, Currently it is 2018 May and quarter 2. So, for quarter 2 the sum of sales should be displayed for only for may. Quarter 1 is alraedy over. So, for quarter 1 the sum of sales should be displayed only for for March.
In 2017, Q4, sum of sales should be displayed only for Dec and so on.
How can I do it?
Tested this and it worked exactly as you want
Table:
LOAD Date,
MonthName(Date) as MonthYear,
Ceil(Rand() * 100) * 10 as Sales,
If((MonthStart(QuarterEnd(Date)) = MonthStart(Date) and MonthStart(Date) < MonthStart(Today())) or MonthStart(Today()) = MonthStart(Date), 1, 0) as Flag;
LOAD Date(MakeDate(2016, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2016, 1, 1) + IterNo() - 1 <= Today();
May be create a flag in the script for this
If((MonthStart(QuarterEnd(DateField)) = MonthStart(DateField) and MonthStart(DateField) < MonthStart(Today())) or MonthStart(Today()) = MonthStart(DateField), 1, 0) as Flag
and then use an expression like this
Sum({<Flag = {1}>}Sales)
Hi Anusha
You could also do the followin
1. Add CalMonthStart to your Calendar object in your script. I assume you have one if you are using Quarters as a filter. eg.
LOAD ....
MonthStart(TransactionDate) as CalMonthStart
Resident ..
2. Then calculate Sales as
=Sum({<CalMonthStart={'$(=Max(MonthStart(TransactionDate)))'}>}Sales)
This will always show sales for only the latest month that has recorded sales (in selected Quarter), which should be fine unless you're recording future sales.
Hope this helps.
Hi Sunny,
Thanks for the response and I tried this.
Now I'm able to see the data for the current month in the current quarter.
But there is one problem here. For the previous quarters, it shows the data only for the last quarter in each year.
Example: I'm able to see the data only for 2017 Q4, 2016 Q4,2015 Q4 and 2016 Q4. Data for rest of the quarters in that year is not displayed.
I want the sum(Sales) for the latest month in each quarter.
Example:
2018 Q2- should display the data for May at this point and it is working.
2018 Q1- should display the data for March 2018.
2017 Q4 - Dec 2017
2017 Q3 - Sep 2017
2017Q2 - June 2017 and so on.
Could you please help me with this?
Thank you for the response. I will try
Tested this and it worked exactly as you want
Table:
LOAD Date,
MonthName(Date) as MonthYear,
Ceil(Rand() * 100) * 10 as Sales,
If((MonthStart(QuarterEnd(Date)) = MonthStart(Date) and MonthStart(Date) < MonthStart(Today())) or MonthStart(Today()) = MonthStart(Date), 1, 0) as Flag;
LOAD Date(MakeDate(2016, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2016, 1, 1) + IterNo() - 1 <= Today();
Hi Sunny,
Thanks a lot. Yes it worked.
One question, I have tried in this way too and observed the same results.
if((Month(QuarterEnd([Date])) = Month([Date]) or Monthstart(Today()) = Monthstart([Date])),1,0)
Do you find any senerio where this expression would fail to give the correct results?
Once again thanks a lot for the help.
Kind regards,
Anusha.
If you don't have future Months in your app, then this should work the same way. I was not sure if you have future Months or not, which is why I gave a solution which should always work.
ok. Thank you.
Also, could you please explain me what is happening in this expression?
=if(GetFieldSelections([Price Period])='Current Month','[Calendar MonthYear] = {"$(vCurrentM)"},',if(GetSelectedCount([Calendar Year]),'[Flag Yearly Data]={"Yes"},','[Flag Current Data]={"Yes"},[Flag Yearly Data]={"Yes"},'))
Seems like this is creating a set analysis to be used in an expression based on different selections made in the dashboard.