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

How to convert SET expression from table to chart ?

Hi All

I have expression :-

=(month*30*Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (STK_OPEN+STK_CLOSE)/2))

/(Sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS_+COGS_INT_)/1))

+

(month*30*Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (AR_CLOSE+AR_OPEN)/2))

/sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (REVENUE+REVENUE_INT_)/1)

-

-1*(month*30*Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (AP_CLOSE+AP_OPEN)/2))

/sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS_+COGS_INT_))

Above expression work fine at Table 1.

I don't know how to create a expression for convert the above to table 1 expression to display as chart. So that chart will display :-

How to make 1,447 display as 139 , when month = 3

How to make 1,387 display as 139 , when month = 2

How to make 1,785 display as 108 , when month = 1

Hope some one can advise me.

Paul

2 Replies
jsaradhi
Valued Contributor

Re: How to convert SET expression from table to chart ?

i notice that your dimensions in Table 1 and Chart 1 are different. so the values will not be the same. can you show a screenshot of what your expected chart looks like even if it is a made up chart in ppt?

paulyeo11
Valued Contributor II

Re: How to convert SET expression from table to chart ?

Hi Sir

The chart i expected already mention in my question , as below :-

How to make 1,447 display as 139 , when month = 3

How to make 1,387 display as 139 , when month = 2

How to make 1,785 display as 108 , when month = 1

The issue i aware is :-

1. YTD Cash Conversion Cycle for Table 1 i display result is wrong , because the average stock i suppose to be from Jan 2015 till Feb 2015 , but now when month= 2 , it only cal Feb value.

In conclusion :-

For SET expression is only capable to use it for sales , as YTD sales for Feb , the SET expression is able to sum up Jan and Feb even when user click on month = 2

For my case is inventory average stock when i click on month = 2 , effectively i only use feb data to cal the average stock , this is not correct !

In another work , if i want to have a field call Target_CCC which is target for Cash conversion cycle , i need to fill in monthly target amount 100 , and use this value to compare with table 1 YTD value , in table it will show it correct , but if i don't click of month =2 only click on year= 2015 , in this case my target will be 100 * 12 = 1200 , which is wrong, because monthly target should be 100 , should not be accumulate.

Hope you understand my issue , as not easy for me explan but i try. 

Finally write till here , i realise that i cannot using SET expression , due to SET expression are only  use for hand sales , for monthly stock in Balance sheet is not same as sales , it cal base jan stock amount sum up Feb stock as total stock for Feb. which is wrong.

Look like i need to spend some time to make my Table 1 value correct first , then can make chart 1 , in order for both correct.

If you happen read till here , and if you have some comment , hope you can share with me.

Paul

Community Browser