Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Applicable88
Creator III
Creator III

Showing SalesTarget only of the actual month we have.

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_MonthRevenue_Target
05.202028170000
05.202028170000
05.202028170000
06.202030000000
06.202030000000
06.202030000000
06.202030000000

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. 

4 Replies
bhaskar_sm
Partner
Partner

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)

 

tt.JPG

Applicable88
Creator III
Creator III
Author

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

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

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.