topic null values in QlikView

null values

himabinduch — Wed, 25 Nov 2020 16:16:04 GMT

HI All,

i have data from 2014Q3to 2019 Q2 and i have one pivot table.

now i want to show data only form 2014 Q3 to 2018Q1 means need to show till current quarter reaming should be null(2018Q2 to 2019Q2) i have attched the sample data .

Thanks in Advance.

Thanks,

hima.

Re: null values

sunny_talwar — Thu, 15 Mar 2018 11:46:55 GMT

Is this the chart you are talking about?

What is the issue here? What do you expect to see?

Re: null values

rubenmarin — Thu, 15 Mar 2018 11:56:25 GMT

Hi Hima, I already see null() values for future quarters.

To hide those dimension values you can use a conditional dimension like:

=If(SHIPPED_DATE<=Today(), YearQuarter_SHIPPED)

Re: null values

himabinduch — Thu, 15 Mar 2018 11:58:49 GMT

Hi sunny,

i want to show data as below

Re: null values

himabinduch — Thu, 15 Mar 2018 12:03:52 GMT

Hi Ruben,

Thanks for your reply

but the above syntax will restrict the data as below . i need that one in Expression (mentioned another screen shot)

i want to display like below.

Re: null values

rubenmarin — Thu, 15 Mar 2018 12:15:09 GMT

There should be something with the data, "Max(TOTAL <YearQuarter_SHIPPED> CREATION_DATE)" returns 'Total Shipped %' for 2018Q1

With a mix of both it seems work but future quarters are hidden:

Conditional dimension: =If(SHIPPED_DATE<=Today(), YearQuarter_SHIPPED)

Expression: RangeSum(Before((count({$<SHIPPED_DATE={'*'},YearQuarter= >}DISTINCT ITEM))/(count(TOTAL <YearQuarter>{$<CREATION_DATE={'*'}>}DISTINCT ITEM)), 0, ColumnNo()))

Re: null values

rubenmarin — Thu, 15 Mar 2018 12:26:04 GMT

This one is close, first quartes shows zero for some rows.

If(Min(TOTAL <YearQuarter_SHIPPED> SHIPPED_DATE)<=Today(), RangeSum(Before((count({$<SHIPPED_DATE={'*'},YearQuarter= >}DISTINCT ITEM))/(count(TOTAL <YearQuarter>{$<CREATION_DATE={'*'}>}DISTINCT ITEM)), 0, ColumnNo())), '-')

Re: null values

himabinduch — Fri, 16 Mar 2018 04:40:38 GMT

Many Thanks Ruben its working for me.

Re: null values

himabinduch — Wed, 21 Mar 2018 06:10:18 GMT

Hi Ruben,

need one more help on the same

can i show the data from 2017Q3 like below

Re: null values

rubenmarin — Wed, 21 Mar 2018 16:31:57 GMT

As each column needs its previous value, removing the initial columns most probably will change the result.

I'm not sure if this can be solved using an as-of table to access previous data:The As-Of Table

The first column expression will need access to the previous values, maybe with an aggr to do the accumulations bt step, some like:

If(Column()=1, Aggr(Expression, YearQuearter), Expression)

Another option can be having the accumulated values precalculated in script.

Re: null values

himabinduch — Fri, 23 Mar 2018 04:23:43 GMT

Hi Rubin,

i had a try but no luck it didn't happen for me.

is there any way to show 7 historic quarters with current quarter every time in my case??