Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to modify long set expression and need 2 Expression for plot 2 line chart into 1 shot expression ?

Hi All

Chart 1 i thing is good. because only 1 expression & expression is very shot , it can plot 2 line.

Sum(

{<Year={'>$(=year(today())-2)'}>}

$(ColumnDim51)/$(Columndim89)/1000)

$(ColumnDim51) allow me to slect display sales , gross profit.

Chart 2 is not so powerful , it require very long expression and 2 expression compare to chart 1.

Money(RangeSum(Above(TOTAL Sum(DISTINCT{<year = {"$(=Max(year)-1)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000), 0, RowNo(TOTAL))), $(vMoneyFormatK))

Money(RangeSum(Above(TOTAL Sum(DISTINCT{<year = {"$(=Max(year)-0)"},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000), 0, RowNo(TOTAL))), $(vMoneyFormatK))

{$(ColumnDim51) allow me to select display revenue or net profit.

Now my question is , how to modify the chart 2 expression and make it only 1 expression by follow chart 1 example ?

Muy next posting will enclosed my QV Doc

Paul

7 Replies
paulyeo11
Master
Master
Author

My QV Doc

sunny_talwar

This expression almost works, but would need you to sort your year field in the script by ascending the first time it is getting created:

Money(Only({<year = {$(=Max(year)-0), $(=Max(year)-1)},$(ColumnDim51) ={$(ColumnDim51)}>} Aggr(NODISTINCT RangeSum(Above(TOTAL Sum(DISTINCT{<year = {$(=Max(year)-0), $(=Max(year)-1)},$(ColumnDim51) ={$(ColumnDim51)}>} Amount*1/ $(Columndim89)/1000), 0, Only({<year>}month))), year, month)), $(vMoneyFormatK))

Right now it is accumulating good up untill month 11, but for some reason stops there. Not sure what is going on. But I guess somebody can make improvements

sunny_talwar

Actually the issue is in month sorting. If you can fix that, the above expression will be fixed. Based on load order the chart object shows this in a table box;

Capture.PNG

I you can fix this in the script to show 1, 2, 3, ...., 12... the above will work.

paulyeo11
Master
Master
Author

Hi Sunny

Appreciated very much for your help . it is okay , i will stick to 2 expression.

Paul

sunny_talwar

Are you sure? There is definitely a way to fix it, but as I mentioned it is script based and since I don't have the database files I won't be able to run it for you. You will have to make that change on your own

paulyeo11
Master
Master
Author

Hi Sunny

I value your help. This morning i wake up , i think over again , yesterday question i post is not correct. my actual requirement is .

When user select select Column51 = $(ColumnDim51) = sales

Chart 1 is working fine with the Expression is below :-

money(

Sum(

{<Year={'>$(=year(today())-2)'}>}

$(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

Now when user select select Column51 = $(ColumnDim51) ={$(ColumnDim51)}>} Amount = rEVENUE

Chart 2 expression need to change , My assume expression is below :-

money(

Sum(

{<Year={'>$(=year(today())-2)'}>}

($(ColumnDim51) ={$(ColumnDim51)}>} Amount)/$(Columndim89)/1000)

, $(vMoneyFormatK))

But it display no line.

Why ?

Paul

paulyeo11
Master
Master
Author

I move this post to top , as now i need to work on issue now.