topic Re: how to convert null values into proper data in QlikView

how to convert null values into proper data

kunkumnaveen — Wed, 12 Jul 2017 09:55:48 GMT

Hello, can any one let me known why i am getting null values even though there is a data

Dimensions :New_Column,PLANT NAME,plant code,inte,int,volume,unit price

Expression: i used

Spend:

Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}SPEND)

Total Spend:

aggr(nodistinct Num(Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}SPEND),'₹ #,##0 '),New_Column,[PLANT NAME])

Total volume:

aggr(nodistinct Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}VOLUME),New_Column,[PLANT NAME])

Thanks

Re: how to convert null values into proper data

kunkumnaveen — Wed, 12 Jul 2017 10:20:34 GMT

i just found out that after removing

[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}

I am able to fill those null values with data ,but according to requirement I need to show previous year values all the time

so is there any thing gone wrong with my syntax..

Re: how to convert null values into proper data

Chanty4u — Wed, 12 Jul 2017 10:22:59 GMT

Sum( {$ <Year={'$(=Year(Today()) - 1)'} > } Sales)

Re: how to convert null values into proper data

kunkumnaveen — Wed, 12 Jul 2017 10:25:37 GMT

HI ,thanks for your reply,

Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}SPEND)

this is my expression ,what i need to change

Re: how to convert null values into proper data

Chanty4u — Wed, 12 Jul 2017 10:43:14 GMT

try to add single quotes

'{$(=max([FISCAL YEAR])-1)}'

Re: how to convert null values into proper data

sunny_talwar — Wed, 12 Jul 2017 11:23:32 GMT

Try these

Spend

Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}SPEND)

Total Spend:

Num(Sum(TOTAL {<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}SPEND),'₹ #,##0 ')

Total volume:

Sum(TOTAL {<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}VOLUME)