topic Re: By Default and Filter Selection on same KPI in Visualization and Usability

By Default and Filter Selection on same KPI

Shahzad_Ahsan — Mon, 10 Feb 2020 06:33:15 GMT

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??

Re: By Default and Filter Selection on same KPI

Kashyap_R — Mon, 10 Feb 2020 06:51:15 GMT

Try nullifying like below give simply the Year inside

Sum({<Date={<filter>},Year=>}Value)

Hope this helps

Thanks

Re: By Default and Filter Selection on same KPI

Shahzad_Ahsan — Mon, 10 Feb 2020 07:13:31 GMT

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.

Re: By Default and Filter Selection on same KPI

Kashyap_R — Mon, 10 Feb 2020 07:20:50 GMT

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

Re: By Default and Filter Selection on same KPI

Shahzad_Ahsan — Mon, 10 Feb 2020 07:49:53 GMT

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)

Re: By Default and Filter Selection on same KPI

Kashyap_R — Mon, 10 Feb 2020 08:01:22 GMT

Can u attach a sample qvf with sample data I will try and get to u soon

Thanks

Re: By Default and Filter Selection on same KPI

Shahzad_Ahsan — Mon, 10 Feb 2020 08:16:03 GMT

Please find the attached Sample qvf and Excel

Re: By Default and Filter Selection on same KPI

Shahzad_Ahsan — Mon, 10 Feb 2020 08:16:53 GMT

Re: By Default and Filter Selection on same KPI

Kashyap_R — Mon, 10 Feb 2020 08:48:02 GMT

Sum({<MyDate={">=$(=date(monthstart(max(MyDate),-6), 'MM/DD/YYYY'))<=$(=date(monthend(addmonths(Today(),-1)), 'MM/DD/YYYY'))"}>} Value)

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