topic Re: How to do calculation in Text Object? in QlikView

How to do calculation in Text Object?

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I am work at a Software Company as a Sales Specialist so I don't have any permission to change anything in the database or add something to the fields so my questions is;

I added a Text Object in my Qlikview 12 and wrote my Sales Target there for the whole year. I also added monthly revenue which can be found below.

= '2016 Sales Target:' & ' ' & '6.860.000 ₺' & '

' & AY & ' ' & 'Status:' & ' ' & money(Sum(SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺')

What I want to do is, subtract my whole year Sales Target from the all months to find which percentage that I am at.

To make sense more; We are currently in June, so I select the whole 6 months from my list box to show what my revenue is at the moment which can be found on by Sum(SATIRNETTUTARI). So I want to subtract 6.860.000 from Sum(SATIRNETTUTARI) but I couldn't find how and also add percentage on another line.

I hope I make sense.

Sincerely,

Re: How to do calculation in Text Object?

sunny_talwar — Thu, 16 Jun 2016 13:17:46 GMT

How about this:

= '2016 Sales Target:' & ' ' & '6.860.000 ₺' & '

' & AY & ' ' & 'Status:' & ' ' & money(Sum(SATIRNETTUTARI)-6860000,'#.##0,00 ₺;-#.##0,00 ₺')

How would the percentage be calculated outside QlikView?

Re: How to do calculation in Text Object?

Thu, 16 Jun 2016 13:21:12 GMT

Thank you for your reply.

I want to do that in another line such as

= '2016 Sales Target:' & ' ' & '6.860.000 ₺' & '

' & AY & ' ' & 'Status:' & ' ' & money(Sum(SATIRNETTUTARI)-6860000,'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Left to Target:' & ' ' & 6860000-money(Sum(SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺')

also in another line I want to show the percentage at where I am at.

I did that line before but it didn't work for some reason.

Re: How to do calculation in Text Object?

sunny_talwar — Thu, 16 Jun 2016 13:54:07 GMT

Try this:

= '2016 Sales Target:' & ' ' & '6.860.000 ₺' & '

' & AY & ' ' & 'Status:' & ' ' & Money(Sum(SATIRNETTUTARI)-6860000,'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Left to Target:' & ' ' & Money(6860000 - Sum(SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺')

Re: How to do calculation in Text Object?

sunny_talwar — Thu, 16 Jun 2016 13:54:39 GMT

How do you calculate the percentage? What is the calculation?

Re: How to do calculation in Text Object?

Thu, 16 Jun 2016 14:34:46 GMT

= '2016 Hedefim:' & ' ' & '6.860.000 ₺' & '

' & AY & ' ' & 'Durum:' & ' ' & money(Sum(SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Hedefe Kalan:' & ' ' & Money(6860000 - Sum(SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Hedefin Yüzdesi:' & ' ' & Sum(SATIRNETTUTARI)/6860000 * 100 & '%'

I did this but the percentage has too many decimals, I only want 2 digits at the end.

Also I want to show the whole 6 months revenue since we're in June. How can I do that?

As you see the picture I selected 6 months and "DURUM" shows me the whole 6 months revenue but when I choose only 1 month, it shows me that months revenue so I want Qlikview to show me the whole 6 months at all times even tho I do not select it the whole 6 months. I hope I make sense.

Re: How to do calculation in Text Object?

sunny_talwar — Thu, 16 Jun 2016 15:16:35 GMT

Try this:

= '2016 Hedefim:' & ' ' & '6.860.000 ₺' & '

' & AY & ' ' & 'Durum:' & ' ' & money(Sum(SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Hedefe Kalan:' & ' ' & Money(6860000 - Sum(SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Hedefin Yüzdesi:' & ' ' & Num(Sum(SATIRNETTUTARI)/6860000, '##.00%')

Re: How to do calculation in Text Object?

Fri, 17 Jun 2016 06:00:16 GMT

Nice, thank you so much!

How about the month part? On the list box, which can be found above, I choose the months from previous until now but I want to have that selected at all times inside of the expression even when I choose 1 month there?

I hope I make sense

Re: How to do calculation in Text Object?

sunny_talwar — Fri, 17 Jun 2016 09:38:38 GMT

So you basically want to ignore selections in Month Field?

= '2016 Hedefim:' & ' ' & '6.860.000 ₺' & '

' & AY & ' ' & 'Durum:' & ' ' & money(Sum({<MonthField = >} SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Hedefe Kalan:' & ' ' & Money(6860000 - Sum({<MonthField = >} SATIRNETTUTARI),'#.##0,00 ₺;-#.##0,00 ₺') & '

' & 'Hedefin Yüzdesi:' & ' ' & Num(Sum({<MonthField = >} SATIRNETTUTARI)/6860000, '##.00%')

Re: How to do calculation in Text Object?

Fri, 17 Jun 2016 11:54:22 GMT

This didn't work. It underlines the whole expression in red.

Re: How to do calculation in Text Object?

sunny_talwar — Fri, 17 Jun 2016 12:04:04 GMT

Really? Can you share a screenshot from Expression Editor

Re: How to do calculation in Text Object?

Fri, 17 Jun 2016 12:08:01 GMT

yea weird

Even tho it underlines it, the data still works, thats another weird thing

what i meant by data working not the months, that text object still works but i still need to select all the months to show the whole revenue until now.

Re: How to do calculation in Text Object?

sunny_talwar — Fri, 17 Jun 2016 12:12:56 GMT

I think you need to replace MonthField with a field from your database where you might be making selection in Months. MonthField was just a placeholder

Re: How to do calculation in Text Object?

Fri, 17 Jun 2016 12:18:32 GMT

hahaha my bad totally forgot about that.

ITS PERFECT You're a life saver. Now, another thing, how can I make the texts bold and underline them?

Re: How to do calculation in Text Object?

sunny_talwar — Fri, 17 Jun 2016 12:32:11 GMT

Do you want the whole thing to be bold and underlined or part of it? Part of it is not possible using inbuilt QlikView functionalities. You will have to either use multiple text boxes with different formats for each of them or use an extension to do this

Re: How to do calculation in Text Object?

Fri, 17 Jun 2016 12:33:33 GMT

All right then I will leave the whole thing as it is.

Thank you and appreciate all the help you have given me. oh and also, thank you for ur patience

Re: How to do calculation in Text Object?

sunny_talwar — Fri, 17 Jun 2016 12:41:51 GMT

No problem at all. I am glad I have been helpful