Showing SalesTarget only of the actual month we have.

[Applicable88]

Hello,

 I want to use a KPI display to show the revenue targets  for the actual month. Somehow all the set expression I used didn't work out.

The Table:

Revenue_Month	Revenue_Target
05.2020	28170000
05.2020	28170000
05.2020	28170000
06.2020	30000000
06.2020	30000000
06.2020	30000000
06.2020	30000000

The KPI Window should always show me the Revenue_Target of my actual month. In May it should show 28170000 and in June the respective target of June. 

So I guess it must be something with the today() function? I think its not going to work because the Revenue_Month is not recognized as a Date in Qlik. Also can it be expressed via Set Analyst?

Please also show me the if-formula to compare  it with the set expression.

Thank you very much. 

[bhaskar_sm]

Hi,

You achieve this by forming you Revenue_Month in a script then use the today() function to pass this in the set expression:

 Script:

TL:
Load *, DATE(DATE#(Revenue_Month,'MM.YYYY'),'MMYYYY') as DATE;
Load * Inline [
Revenue_Month, Revenue_Target
05.2020, 28170000
06.2020, 30000000
];

KPI Set Expression:

vToday=date(Today(),'MMYYYY')

sum({< DATE={$(vToday)} >} Revenue_Target)

[Applicable88]

Hi Bhasker_smu,

thanks for the quick reply. I don't think thats going to work, because my data isn't static.

Its an ongoing webdata not "fixed" data. 

Is there also a way to change that colum into a date column that Qlik understands its a "DATE" ? 

I also have timestamps on that same table, but they have subsets at the end/beginning of a new month. 

I hope there is an "easier" solution. 

[bhaskar_sm]

Hi,

you can change that column into date by using makedate() function or you can add first day to the existing column and then use date function to format

Example:

Date#(1&’.’&Datecolumn,’DD.MM.YYYY’) as date

make date function:This function returns a date calculated from the year YYYY, the month MM and the day DD.

Syntax:  MakeDate(YYYY [ , MM [ , DD ] ]

[Florences]

This creates a more palatable goal that your team can monitor closely walmartone. It also allows you as the manager to notice things are slipping off the tracks before your quarterly goal is completely derailed.