Bar chart dimension not using year how to make it using year ?

[paulyeo11]

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

[paulyeo11]

My QV Doc

[sunny_talwar · Accepted solution]

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

[Anonymous]

Wow !!!  That is some data model.

I'd suggested looking at streamlining the data model.

[paulyeo11]

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

[paulyeo11]

‌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

[sunny_talwar]

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.

[paulyeo11]

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