22 Replies Latest reply: Mar 21, 2016 6:48 AM by Tamil arasu

# How to create fixed value KPIs?

Hi all!

I have some daily sales KPIs.

But everytime users click on few slections, it changes the daily sales KPIs ..

what can i do to those daily sales KPIs that value will remain unchanged even user have click on filters? In short, i need to fixed daily sales KPIs...

Rgds,

Jim

• ###### Re: How to create fixed value KPIs?

Hi Jim,

Add {1} in your expression like below. It will ignore the selections.

Sum( {1} Sales)

• ###### Re: How to create fixed value KPIs?

if i already have a long experssion as such, where shuold i put the {1} ?

=num(sum({<Date = {'\$(=Date(today()-1))'}>} [my revenue])/1000000, '#,###')& ' mil'

Rgds

Jim

• ###### Re: How to create fixed value KPIs?

Like below

=num(sum({1<Date = {'\$(=Date(today()-1))'}>} [my revenue])/1000000, '#,###')& ' mil'

• ###### Re: How to create fixed value KPIs?

I seee.. add before <Date> , modifier rite?

• ###### Re: How to create fixed value KPIs?

Yes. If you are not using any set analysis, then simply add {1} in your expression like below.

Sum({1} Sales)

{1} means ignore selections. For example, sum({1} sales) would return the same value regardless of selections made in any dimensions.

If you are using  set analysis, then simply add 1 in you expression like below.

=num(sum({1<Date = {'\$(=Date(today()-1))'}>} [my revenue])/1000000, '#,###')& ' mil'

• ###### Re: How to create fixed value KPIs?

Hello Tami, i have tried adding "1" in my expression as below:

this is my "last week sales" KPI. when i clicked on other dimension ( such as another month or year), it changed.

=num(sum({1<Date={">=\$(=weekstart(max(Date),-1))<=\$(=weekend(max(Date),-1))"}>} [my revenue])/1000000, '#,###')& ' mil'

• ###### Re: How to create fixed value KPIs?

Jim,

In this case, you have to interchange the position of {1}  like below,

=num(sum({<Date={">=\$(=weekstart(max( {1} Date),-1))<=\$(=weekend(max( {1} Date),-1))"}>} [my revenue])/1000000, '#,###')& ' mil'

you have to use {1} (not 1) , Since there is no set expression inside the Max function,

• ###### Re: How to create fixed value KPIs?

i have tried yours , but it still can changed when clicking diff filters/dimensions,.

however this one, i replaced the "\$" with 1. but i am not sure is it correct or not...

Sum( {1<Date={">=1(=weekstart(max(Date),-1))<=1(=weekend(max(Date),-1))"}>} [Actual GSV])

• ###### Re: How to create fixed value KPIs?

Fine. Try below expression.

=num(sum( 1 {<Date={">=\$(=weekstart(max( {1} Date),-1))<=\$(=weekend(max( {1}Date),-1))"}>} [my revenue])/1000000, '#,###')& ' mil'

• ###### Re: How to create fixed value KPIs?

i have " Error in expression" at here =num(sum( 1 {<Date={

• ###### Re: How to create fixed value KPIs?

=num(sum( {1 <Date={">=\$(=weekstart(max( {1} Date),-1))<=\$(=weekend(max( {1}Date),-1))"}>}  [my revenue])/1000000, '#,###')& ' mil'

this one worked.

• ###### Re: How to create fixed value KPIs?

Oops.. I just placed the 1 in wrong place and glad you found it. Hope you understood the logic.

• ###### Re: How to create fixed value KPIs?

trying to. this one is a "Last Month" kpi expression. do you think is correct, Tamil?

=Num(Sum({1<Date={'(= MonthName(AddMonths(Today(),-1)))'}>}  [my revenue])/1000000, '#,###') & ' mil'

• ###### Re: How to create fixed value KPIs?

Yes. That's right Jim.

• ###### Re: How to create fixed value KPIs?

Tamil. i am thinking.. is it possible that only ignore certain dimensions?

example - my yesterday,last week, last month sales KPIs, i can ignore period selection like - year month date quarter.

however, what if my user want to select a filter like  country, maybe Russia.

Rgds,

Jim

• ###### Re: How to create fixed value KPIs?

Hi Jim,

Yes. That's possible. You can add like below.

=sum({<Year =, Month =, Quarter=, Date = {'\$(=Date(today()-1))'}>} [my revenue])/1000000

1. If you want to ignore all the selections use {1} in you expression.

2. If you want to ignore certain dimensions, use FieldName =, in your expression.

Below link might be helpful for you.

Ignore all selections except some specific fields using Set Analysis

• ###### Re: How to create fixed value KPIs?

I seee. thank you so much for your lesson today, Tamil.

so, only if i want to ignore all, then only use {1} . certain dimension , use fieldname=

thanks Tamil.

• ###### Re: How to create fixed value KPIs?

Tamil,

but i think the date - has to be ignored. am rite i? so meaning i will have to add {1} in this expression. ?

Date = {'\$(=Date(today()-1))'}>}

Rgds

Jim

• ###### Re: How to create fixed value KPIs?

Yes. My first solution is answer for the above question.

=num(sum({1<Date = {'\$(=Date(today()-1))'}>} [my revenue])/1000000, '#,###')& ' mil'

• ###### Re: How to create fixed value KPIs?

Tamil ,

Can i ask you another question ?

how can i set the qvw file to as of today ??? exmaple, today is 21/03. when i open the qvw file, it will display 21 of march data to me , by default.

Rgds,

Jim

• ###### Re: How to create fixed value KPIs?

Sure .

You can use triggers to select the current date when you open the application.Try below procedure.

Document Properties -> Triggers -> Document Event Triggers -> OnOpen -> Add Actions ->Selection -> Selection Field -> Type your field name and Search string as Today(). (Format the according to your date field i.e Date(Today(),'DD/MM/YYYY') )

When you re-open the application ,the data will be filtered to current date.

• ###### Re: How to create fixed value KPIs?

thanks for this one , tamil!