Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guru's,
i want to push below calculated Dimension into Script Level.
Dual(Country, Aggr(NODISTINCT Sum([Actial Headcount]), [Business Unit], Country))
How to achieve This.
Please find the attached Data File,Qvf File.
Thanks,
Joshi
Hi Joshi!
You have two ways, depending on your needs.
- Why do you need to push it in script level?
- Why do you need "DUAL"? In the table, you only use the "Country" value (String side of the Dual)
If you need it for sorting purposes, don't forget you can set the order of each field in "By Expression". So, if your expression Aggr(NODISTINCT Sum([Actual head count]), [Business Unit], Country) is the order:
- The field should be just Country
- In Sorting, select these field, and click on "By expression", and in "Expression" box put your one.
If this expression is for order purposes, what is the order do you need?
Hi my sort order on country field is like below
API ITA origg
API.ITA . ...
......
API US location
API US location
Similar to what I am getting right now in app .need same order with out calculated dimension
Thanks
Try Somehting like this -
Data:
LOAD
"Business Unit",
Country,
"Location / Cost Center",
"2016 EOY Headcount",
"2016 EOY Headcount1",
"Actual head count"
FROM [lib://Desktop/Data file.xls]
(biff, embedded labels, table is Sheet1$);
left join(Data)
BU_Sort_Order:
LOAD * Inline [
SORT_ORDER,Business Unit
1,API
2,DDA
3,DP
4,Fine Chemicals
5,Corporate
];
NoConcatenate
NewTable:
Load "Business Unit",
Country,
"Location / Cost Center",
"2016 EOY Headcount",
"2016 EOY Headcount1",
SORT_ORDER,
Dual(Country, Sum([Actual head count])) as DualledCountry
resident Data
group by
"Business Unit",
Country,
"Location / Cost Center",
"2016 EOY Headcount",
"2016 EOY Headcount1",
SORT_ORDER,
;
Drop table Data;
KRISHNA Thanks for your time.
still no luck.Sort order missing a bit .can you post your working qvf file.
US Head count is 431
ITA Headcount is 505 so ,ITA Should show as first country and next US
Desired Outout :
API ITA Location(230)
API.ITA . Location(148)
API US Location(294)
API US Location(71)
Please find the qvf file of your code