Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
Right now my chart i make use of 2 expression to plot the CY & LY bar , with out using year at Diamension :-
1st expression for LY
=(If(sum(DISTINCT STK_CLOSE) > 0 and sum(DISTINCT STK_OPEN) > 0,
RangeAvg(Only({<year = {$(=max(year)-1)}>}Aggr(Sum({<month, year>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year)),
sum({<month, year, YearMonth = {"$(=Dual(Date(YearEnd(AddYears(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), -1)), 'YY MMM'), MonthStart(YearEnd(AddYears(Max({<STK_CLOSE = {'*?'}>}YearMonth), -1)))))"}>} DISTINCT STK_CLOSE)))
/
If(sum(DISTINCT STK_CLOSE) > 0 and sum(DISTINCT STK_OPEN) > 0, RangeSum(Above(Sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} DISTINCT (COGS+COGS_INT_)/1), 0, RowNo())))
)
*month*30
2nd expression for CY
=If(sum(DISTINCT STK_CLOSE) > 0 and sum(DISTINCT STK_OPEN) > 0,
RangeAvg(Aggr(Sum({<month>}DISTINCT TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year),
sum({<month, YearMonth = {"$(=Dual(Date(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), 'YY MMM'), MonthStart(Max({<STK_CLOSE = {'*?'}>}YearMonth))))"}>}DISTINCT STK_CLOSE))/
RangeSum(Above(Sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} DISTINCT (COGS+COGS_INT_)/1), 0, RowNo())))
*
month*30
May i know how to combine the above 2 expression into 1 , so that Diamension i can use year to plot the bar chart.
Paul
Try this expression:
=(RangeAvg(Only({<year = {$(=max(year)-1), $(=max(year))}>}Aggr(Sum({<month, year>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year)),
sum({<month, year, YearMonth = {"$(=Dual(Date(YearEnd(AddYears(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), -1)), 'YY MMM'), MonthStart(YearEnd(AddYears(Max({<STK_CLOSE = {'*?'}>}YearMonth), -1)))))"}>+
<month, year, YearMonth = {"$(=Dual(Date(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), 'YY MMM'), MonthStart(Max({<STK_CLOSE = {'*?'}>}YearMonth))))"}>} DISTINCT STK_CLOSE))
/
Sum({$<year = {$(=max(year)-1), $(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} DISTINCT (COGS+COGS_INT_)/1))
*Only(TOTAL month)*30
My QV Doc
Try this expression:
=(RangeAvg(Only({<year = {$(=max(year)-1), $(=max(year))}>}Aggr(Sum({<month, year>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year)),
sum({<month, year, YearMonth = {"$(=Dual(Date(YearEnd(AddYears(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), -1)), 'YY MMM'), MonthStart(YearEnd(AddYears(Max({<STK_CLOSE = {'*?'}>}YearMonth), -1)))))"}>+
<month, year, YearMonth = {"$(=Dual(Date(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), 'YY MMM'), MonthStart(Max({<STK_CLOSE = {'*?'}>}YearMonth))))"}>} DISTINCT STK_CLOSE))
/
Sum({$<year = {$(=max(year)-1), $(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} DISTINCT (COGS+COGS_INT_)/1))
*Only(TOTAL month)*30
Wow !!! That is some data model.
I'd suggested looking at streamlining the data model.
Hi Sunny
Thank you for your effort in try to help to try to solve the problem , but the result was wrong ..... wait let me check again ... Edited ( As yesterday your expression is not work when you reply me ) . To my surprise your expression was working fine.
Now i must said that your mind is very powerful. i cannot even imagine this is possible to solve. Many thank.
Paul
Hi sunny
May I know how Long you take to come out the very complicated expression ?
As I am still struggle to understand how you make it , in order for me to apply to my other chart .
MAy I know your QV experience in QV ?
Paul
Paul -
It takes a while to understand the data model, but I usually have an idea of what could be a potential solution. From there onwards its a mixture of breaking the expression down into small piece and doing some trial and error. I have little under 1 and half year experience in QV.
Hi Sunny
I see , yes this is the approach to solution the complicate expression.
Me too I usually break the complicated formula into part by part and try to solve it.
I see you are very fast learner , no wonder I never see you last time , when I started I know quite a number QV expert like john w... Robert , micheal etc
Paul
Sent from my iPhone