Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi--
Fairly new to QV and was wondering if the following is possible during a data load.
In my source data a year's budget is 12 entries for a year with the total budget divided up:
Year Account Budget Period
2014 Revenue 500 1
2014 Revenue 500 2
2014 Revenue 500 3
2014 Revenue 500 4 (etc).
In reality the Revenue budget = 500*12 so 6000.
When bringing into QV I would like to bring in only.
2014 Revenue 6000
Is this something possible during a load or will I need to total the budget numbers outside of QV before loading them (doable would just like to do this all w/in QV).
Thanks in advance - still very new to QV!
Write a expression like
In Dimension:-
Year,
Account
In Expression
Sum(DISTINCT Budget) * COUNT(Period)
Hope this helps
Thanks & Regards
Will this work for you?
You can calculate this way also
T:
LOAD * Inline
[
Year,Account,Budget,Period
2014,Revenue,500,1
2014,Revenue,500,2
2014,Revenue,500,3
2014,Revenue,500,4
2014,Revenue,500,5
2014,Revenue,500,6
2014,Revenue,500,7
2014,Revenue,500,8
2014,Revenue,500,9
2014,Revenue,500,10
2014,Revenue,500,11
2014,Revenue,500,12
];
Left Join
LOAD
Year,
Account,
sum(DISTINCT Budget) * Count(Period) as Revenue
Resident T
Group by Year,Account;
EXIT Script;
In Dimension
Year,Account
Expression
Revenue
Hope this helps
this worked in my tables - It may be the best way to go. Thanks