Help with a table and a formula (differentiating currency, absolute numbers and %)
Hello everyone,
I need help with the following formula.
So the thing is, I have a dataset with a column called Measure, and then four other columns called Q1,Q2,Q3 and Q4.
In the measure columns there are different categories, some of them are % while others are absolute numbers.
I need to show them in a way that I have the following columns in Qlik:
Measure Q1 Q2 Q3 Q4
I was succeful with that with the following formula, with I used it as a dimension in my table, while I used Q1, Q2, Q3 and Q4 as measures…
The problem is, that all the values in my table are shown as absolute numbers, and it should not be like that, some should be shown as currency some others as % and some others as absolute numbers.
What can i do? this is my formula.
=If(Len(Trim(Measure)) > 0
AND Match(Measure, 'Sales ($)', 'Number of shops', 'Number of shops %' , 'Number of shops %,' Employees', 'Bosses',
'clients', 'entries', 'revenue $'),
Dual(Measure, Match(Measure, 'Sales ($)', 'Number of shops', 'Number of shops %', ' Employees', 'Bosses',
'clients', 'entries', 'revenue $'))
)
So, 'Sales ($)' should be shown as currency, 'Number of shops' as absolute number, 'Number of shops %' as % , ' Employees' as absolute number, 'Bosses' as absolute number, 'clients' as absolute number, 'entries' as absolute number, and 'revenue $' as currency
You can use the num() function to format values. But to help you further it would be great with some example rows of what the raw data looks like in the backend table, and what result you want in the displayed table in frontend.
