Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to write set analysis for my nested if

=  if(Year_Date = MaxYear,

      if(Sum([Total Services Charges]) > 100000,  sum((Num([Total Services Charges]/1000000,'$#,##0.00;($#,##0.00)'))) & ' M',

        if(Sum([Total Services Charges]) > 1000, sum(Num([Total Services Charges]/1000,'$#,##0.00;($#,##0.00)')) & ' K',

           sum(Num([Total Services Charges]/1000,'$#,##0.00;($#,##0.00)')) & ' K')

           )

           )

1 Reply
Digvijay_Singh

Looks like you want to format the number to M/K based on its value, I don't think you can simplify it using set analysis as you have to check each aggregated Sum value to decide if you want to write M or K after that.

One approach could be to create island table of units M,K,Unit and allow user to select a unit user would like to see their numbers in , the other field in the same island table is the value in M, K and 1, which will be used to divide the sum expression. This will avoid the if statements but all the numbers will appear in single unit.