Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Show value of the right month, of today

Hello,

newbie question again...

I have one table:

Revenue_MonthRevenue_Target
05.202028170000
05.202028170000
05.202028170000
06.202030000000
06.202030000000
06.202030000000
06.202030000000

I want a KPI Display window which always shows me the "Revenue_Target" of the actual month. So if today would be May it shouws me the target of may, and next Month June it automatically shows me the "revenue_target" of June. I just need to display the value, no sum aggregation needed. 

I tried something like:

if([Revenue_Month]=today(), [Revenue_Target]) or

 if(monthname(today())=monthname([Revenue_Month]), Revenue_Target)

But I got empty values. I might wrote something wrong. Or maybe the today and monthname functions cannot work with a format like MM.YYYY?

Can someone also show me a correct if-fuction and maybe also how to write in correctly as a SetExpression/Set analys if possible?

Stay safe!

Thanks in advance. 

 

19 Replies
Highlighted
Master
Master

Re: Show value of the right month, of today

May be :

=sum({<Revenue_Month={"$(=Date(today(),'MM.YYYY'))"}>} Revenue_Target)
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
Creator II
Creator II

Re: Show value of the right month, of today

Hi, 

thank you for the quick reply, but thats not exactly what I need. You are summing all the Revenues of today's month in the list. But I only need the value ONCE.

Any idea?

Thank you. 

Highlighted
Master
Master

Re: Show value of the right month, of today

Hi,

if I understood correctly

=Only({<Revenue_Month={"$(=Date(today(),'MM.YYYY'))"}>} Revenue_Target)
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
Creator II
Creator II

Re: Show value of the right month, of today

hi,

I don't know why, but with only() function it returns  no value.  I have no clue why.

Can you express your function as a if-function?

 

=Only({<Revenue_Month={"$(=Date(today(),'MM.YYYY'))"}>} Revenue_Target)

 

Seemingliy when I try to write as a If-function my syntax is not properly:

if( Revenue_Month={"$(=Date(today(), 'MM.YYYY'))"} Revenue_Target)

How to write this correctly?

Highlighted
Partner
Partner

Re: Show value of the right month, of today

Hi, You should use:

=Sum({<Revenue_Month={"$(=Date(today(),'MM.YYYY'))"}>} Distinct Revenue_Target)

 

Regards,

Gabriel

Highlighted
Master
Master

Re: Show value of the right month, of today

To be sure you understand ?

can you send me the expected output ?

you want a formula for Dimension or Measure ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted

Re: Show value of the right month, of today

I am replying to you from your last response. May be try this way

if( Revenue_Month=Date(today(), 'MM.YYYY'), Revenue_Target)

I assume, Revenue_Month in the format of MM.YYYY only.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

Re: Show value of the right month, of today

Hi,

 

 

Revenue_Month and Revenue_Target I of course already had replaced with my "Umsatzmonat" and "Umsatzziel" before putting in your formulas. You see month April "04.2020" always has value "29490000" and May "05.2020" always has "28170000"

Umsatzmonat is a Date format and Umsatzziel is a measure.

I also don't understand why using your Only-formula with my dimension names isn't working, since there is always the same value in Revenue_Target for May.  

=Only({<Revenue_Month={"$(=Date(today(),'MM.YYYY'))"}>} Revenue_Target)

I just want the KPI windows display always the value of the month we now have. Since now is may it should be llike that:

 

1000000

 

With the only function I get the "NV" sign like this"-" .

And with your Sum(function) I guess that I get all the cells  with the month revenue value of 25170000 and the month 05.2020 date summed up, because the amount is very high:

 

I hope you know a solution.

 

 

 

 

Highlighted
Creator II
Creator II

Re: Show value of the right month, of today

Hi,

thank you for your help, but also this doesnt work.

I dont't get any returns with your function. There might be something missing?

I put my real data as screenshot. Maybe together we can find the right anser.