Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
pjpkumar26
Contributor III
Contributor III

Formatting

Hi Gurus,

I have created the below expression and its works as expected:

sum(aggr(sum(aggr(Count(distinct[Emp]),[Date],[Dep],[Com])),[We]))/
count(total<[We]>distinct[Date])

I get the value 1278,000,0

but i need this value to be 1278

How can I get this please.

Many Thanks,

Kumar

1 Solution

Accepted Solutions
Andrei_Cusnir
Specialist
Specialist

Hello,

 

Can you please share with us a screenshot of how it looks like on your side? It will allow us to understand where exactly this value is presented and how else can we proceed further. My current hypothesis is that additional formatting is applied on the response. 

 

For example:

  1. If the formatting is applied on measure itself.
  2. If the formatting is applied on Master item measure.
  3. etc.

 

Try expanding the measure and check the con figuration there:

 

For example on my side, it is set to auto and thus the number is "1278". If I set it to custom then my number changes to "1,278.00". So on your side most probably this is what causing the number to appear as it does, regardless of the formatting you pass in the expression.

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

8 Replies
Andrei_Cusnir
Specialist
Specialist

Hello,

 

Have you tried wrapping it into Num() function? Like Num(EXPRESSION) ?

 

For example:

=Num(sum(aggr(sum(aggr(Count(distinct[Emp]),[Date],[Dep],[Com])),[We]))/
count(total<[We]>distinct[Date]))

 

On my side the output is:

NOTE: You can also take a look at the official documentation page [1], where it shows how to use custom formatting with the function, to choose how the output number will look like.

---

[1] https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/Scripting/FormattingFunct...

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
pjpkumar26
Contributor III
Contributor III
Author

Hi Andrei,

I tried Num at my end and it still show the same value 1278,000,0

I used this:
=Num(sum(aggr(sum(aggr(Count(distinct[Emp]),[Date],[Dep],[Com])),[We]))/
count(total<[We]>distinct[Date]))

Thanks,

Kumar

Andrei_Cusnir
Specialist
Specialist

Hello,

 

Can you please share with us a screenshot of how it looks like on your side? It will allow us to understand where exactly this value is presented and how else can we proceed further. My current hypothesis is that additional formatting is applied on the response. 

 

For example:

  1. If the formatting is applied on measure itself.
  2. If the formatting is applied on Master item measure.
  3. etc.

 

Try expanding the measure and check the con figuration there:

 

For example on my side, it is set to auto and thus the number is "1278". If I set it to custom then my number changes to "1,278.00". So on your side most probably this is what causing the number to appear as it does, regardless of the formatting you pass in the expression.

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
pjpkumar26
Contributor III
Contributor III
Author

Hi Andrei,

Apologies ! its my bad I have selected Number at my end and once I changed to Auto it showing the correct value 1278.

Many Thanks,

Kumar

MatheusC
Creator
Creator

Olá,  @pjpkumar26 

 

Você pode tentar usar essa expressão

 

if(soma(aggr(soma(aggr(Count(distinct[Emp]),[Data],[Dep],[Com])),[We]))

/
count(total<[Nós]>distinto[ Dados] )  >1E6,

num( soma(aggr(soma(aggr(Contagem(distinta[Emp]),[Data],[Dep],[Com])),[We]))

/
count(total<[ Nós]>distinto[Data]) )/1E6,'#,00 M')

 

Resultado:

MatheusC_0-1663335933401.png

 

Caso tenha ajudado a marcar como solução e deixe um like

 

Grato!

 

MatheusC
Creator
Creator

 

MatheusC_1-1663336952107.png

 

 Matheus C 
 Contribuinte III
 

 

 

Olá,  @pjpkumar26 

 

if(soma(aggr(soma(aggr(Count(distinct[Emp]),[Data],[Dep],[Com])),[We]))

/
count(total<[Nós]>distinto[ Dados] )  >1E6,

num( soma(aggr(soma(aggr(Contagem(distinta[Emp]),[Data],[Dep],[Com])),[We]))

/
count(total<[ Nós]>distinto[Data]) )/1E6,'#,00 M')

 

Caso tenha ajudado a marcar como solução e deixe um like

 

Grato!

pjpkumar26
Contributor III
Contributor III
Author

pjpkumar26_0-1663337302635.png

I get this when i use the above syntax - can't see the value

Regards

MatheusC
Creator
Creator

Olá, @pjpkumar26 

Veja a a essa sintaxe

if(

sum(aggr(sum(aggr(Count(distinct[Emp]),[Data],[Dep],[Com])),[We]))
/
count(total<[Nós]>distinct[ Dados] )>1E6,

num(sum(aggr(sum(aggr(count(distinct[Emp]),[Data],[Dep],[Com])),[We]))

/
count(total<[ Nós]>distinct[Data])/1E6,'#,000 M')
)

 

Aqui utilizei um teste e está funcionando bem.