Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a KPI which is showing last 6 months sales from current date.
I have "Year" filter which contains last 10 years of data.
If I select Year=2018, then same KPI should show 2018 sales. For now it shows 0 because the expression is restricted to last 6 months only.
Can we manage both with same KPI??
Hi
Try nullifying like below give simply the Year inside
Sum({<Date={<filter>},Year=>}Value)
Hope this helps
Thanks
Hi
Thanks for your response.
I didn't get it
Here is my expression which shows last 6 months data
Sum({<MyDateField={">=$(=date(floor(monthstart(addmonths(Today(),-6))), 'MM/DD/YYYY'))<=$(=date(floor(monthend(addmonths(Today(),-1))), 'MM/DD/YYYY'))"}>} VALUE)
How can I see previous data on behalf of filter selection
If I select "Year" filter, it should ignore this 6 month condition and return data on behalf of filter selection.
try this
Sum({<MyDateField={">=$(=date(floor(monthstart(addmonths(Today(),-6))), 'MM/DD/YYYY'))<=$(=date(floor(monthend(addmonths(Today(),-1))), 'MM/DD/YYYY'))"},Year=>} VALUE)
In the Year place if you have Year as your field then you can use that else the field Name like FiscalYear etc whichwvwe the field name used as filter
Hope this helps
Thanks
Hi
No, its not working
It is showing correct value for last 6 months but when I select any "Year" from filter the value doesn't change.
My Expression:
Sum({<MyDateField={">=$(=date(floor(monthstart(addmonths(Today(),-6))), 'MM/DD/YYYY'))<=$(=date(floor(monthend(addmonths(Today(),-1))), 'MM/DD/YYYY'))"},Year_Prop=>} Value)
Hi
Can u attach a sample qvf with sample data I will try and get to u soon
Thanks
Please find the attached Sample qvf and Excel
Hi
Sum({<MyDate={">=$(=date(monthstart(max(MyDate),-6), 'MM/DD/YYYY'))<=$(=date(monthend(addmonths(Today(),-1)), 'MM/DD/YYYY'))"}>} Value)
or
Sum({<MyDate={">=$(=date(monthstart(max(MyDate),-6), 'MM/DD/YYYY'))<=$(=date(monthend(addmonths(max(MyDate),-1)), 'MM/DD/YYYY'))"}>} Value)
I don't think it will work if you take Today() for both conditions. Instead, if data is updating regularly you can take max(Date)
Hope this helps
Thanks