Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

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

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

7 Replies
paulyeo11
Valued Contributor II

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 ?

Wow !!!  That is some data model.

I'd suggested looking at streamlining the data model.

paulyeo11
Valued Contributor II

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

paulyeo11
Valued Contributor II

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.

paulyeo11
Valued Contributor II

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

Community Browser