Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have more than 30 measures which has to be calculated row level, i have tried pivot table by putting the 30 measures expressions using "if" condition in one column below is the sample expression for 6 measures, but it has limitation on no.of lines/characters in set expression, Qliksense pivot only excepts somewhere around 8192 characters including spaces and which handles few measures only.
if([Category-Description]='Budgeted (Sales Revenue)', SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"} >}Target), if([Category-Description]='Budgeted (TON)', SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target), if([Category-Description]='Avg Selling P/TON Target', SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target), if([Category-Description]='Budgeted (Mtrl Cost)', SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target), if([Category-Description]='Budgeted (DM Contribution)', SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target), if([Category-Description]='Budgeted (Contribution %)', ((SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target))/(SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)))*100, ))))))
Can anyone advice me how do i handle all the measures in one measure either through load script/variable/master measures.
Thanks,
MAK.
One way to save up a few characters is to use the Pick(Match()) combo instead of the nested IFs:
Pick(Match([Category-Description],'Budgeted (Sales Revenue)','Budgeted (TON)','Avg Selling P/TON Target','Budgeted (Mtrl Cost)','Budgeted (DM Contribution)','Budgeted (Contribution %)', SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"} >}Target), SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target), SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target), SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target), SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target), ((SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)-SUM({<Category = {'Material Cost'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target))/(SUM({<Category = {'Sales Revenue'}, YEAR = {"$(=YEAR(today()))"}>}Target)/SUM({<Category = {'Volume in Ton'}, YEAR = {"$(=YEAR(today()))"}>}Target)))*100 ))
Hi,
Thanks for your reply,
i believe here pickmatch will not work, because "Category-Description" is an inline load script and rest comes from transaction table.
Thanks,
MAK.
The suggestion from fosuzuki will work in the same way like your nested if-loops - it's only a different and better way to handle multiple of such conditions.
Personally I would probably use another approach by extending the inline-table to another field which contained your filter values, like:
t: load * inline [
desc, value
Budgeted (Sales Revenue), Sales Revenue
...
];
and then you could grab this value - maybe with p() like:
SUM({<Category = p(value), YEAR = {"$(=YEAR(today()))"} >}Target)
- Marcus