7 Replies Latest reply: Feb 19, 2016 8:59 PM by Yeo Poh sai

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

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

• ###### Re: Bar chart dimension not using year how to make it using year ?

My QV Doc

• ###### Re: Bar chart dimension not using year how to make it using year ?

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

• ###### Re: Bar chart dimension not using year how to make it using year ?

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

• ###### Re: Bar chart dimension not using year how to make it using year ?

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

• ###### Re: Bar chart dimension not using year how to make it using year ?

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.

• ###### Re: Bar chart dimension not using year how to make it using year ?

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

• ###### Re: Bar chart dimension not using year how to make it using year ?

Wow !!!  That is some data model.

I'd suggested looking at streamlining the data model.