Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

7 Replies
paulyeo11
Master
Master
Author

My QV Doc

sunny_talwar

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
Not applicable

Wow !!!  That is some data model.

I'd suggested looking at streamlining the data model.

paulyeo11
Master
Master
Author

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
Master
Master
Author

‌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
Master
Master
Author

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