topic Re: How to convert SET expression from table to chart ? in QlikView

How to convert SET expression from table to chart ?

paulyeo11 — Thu, 02 Apr 2015 17:34:08 GMT

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

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

Anonymous — Thu, 02 Apr 2015 19:28:25 GMT

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?

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

paulyeo11 — Fri, 03 Apr 2015 02:01:54 GMT

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