topic Re: Show all DEPARTMENT`s even if value is null in QlikView

Show all DEPARTMENT`s even if value is null

razvan_brais — Thu, 08 Mar 2018 08:24:28 GMT

Hey guys,

Hope you can help me with some problems.

I`m trying to display in the same time all sales of different kpi`s for each department id for current day and previous day. But the problem is that when I add them in the same chart it shows me only department id`s from current day.

When I`m adding all the info from FINAL_TABLE in a pivot table , all values for current day (POINT IN TIME = 'CD') are correct , but for

previous day (POINT_IN_TIME = 'PD') are not so correct.

What I have noticed is that I have some differences on some kpi`s because for the 'PD' are not brought all DEPARTMENT_ID`s. In the table are kept only the DEPARTMENT_ID for 'CD' .

I`m adding an Excel File and a qvw project in which I kinda show what I want to obtain.

The table with two expression is what I get , and in the second table is what I want to have on the second expression from first table. Hope you can help me.

One idea that comes in mind is that i need to set DEPARTMENT_ID`s value to 0 if there is no data . But I don`t know how can I do that in the script.

Can you please help me on what to do in script that I can have all values?

Thank you,

Razvan.

Re: Show all DEPARTMENT`s even if value is null

kevinalvino — Thu, 08 Mar 2018 10:35:55 GMT

Hi razvan.brais,

If i dont get your question wrong , i take it CD mean current and PD is Previous, so you just need correct value for PD*

Then i think, Solution should be this:

Sum ({<DATE={"$(=Date(Max(DATE)-1,'DD/MM/YYYY'))"}>} Value)/1000

Best Regards,

Kevin

Re: Show all DEPARTMENT`s even if value is null

sunny_talwar — Thu, 08 Mar 2018 12:42:55 GMT

Check the attached

Script

TestTable:

LOAD DATE as HIST_DATE,

kpiNo,

Name_Kpi,

DEPARTMENT_ID as UNIT_ID,

Value01 as Value

FROM

TestExcel.xls

(biff, embedded labels, table is Sheet1$);

temp:

LOAD UNIT_ID,

Sum(Value) as Value,

Date(Floor( HIST_DATE ))as HIST_DATE,

Name_Kpi,

kpiNo,

Month(HIST_DATE) as Month,

Year(HIST_DATE) as Year

Resident TestTable

Group by UNIT_ID, HIST_DATE, Name_Kpi, kpiNo;

DROP Table TestTable;

AsOfTable:

LOAD DISTINCT HIST_DATE as AsOfHistDate,

HIST_DATE,

'CD' as PointInTimeVersion

Resident temp;

Concatenate (AsOfTable)

LOAD DISTINCT HIST_DATE as AsOfHistDate,

Date(HIST_DATE - 1) as HIST_DATE,

'PD' as PointInTimeVersion

Resident temp;

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Thu, 08 Mar 2018 13:46:38 GMT

This is perfect. Thanks a lot . I made it to the same result but with more script code

Now I want to ask another question .

Lets say if I have as date 27,26,24,23. And I pick for current day 26 , for previous day it will be 25 , but I don`t have values for this date. Normally previous day should be 24 (the date where I have values) . Using HIST_DATE - 1 will bring 25th.

Any suggestion on how to do this work?

Thanks again

Re: Show all DEPARTMENT`s even if value is null

sunny_talwar — Thu, 08 Mar 2018 13:58:32 GMT

You don't have value because it is a weekend or holiday or you randomly don't have values?

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Thu, 08 Mar 2018 13:59:49 GMT

Weekend or Holiday.

Re: Show all DEPARTMENT`s even if value is null

sunny_talwar — Thu, 08 Mar 2018 14:04:56 GMT

Do you have a list of Holidays stored somewhere in your dashboard? If you do then you can use FirstWorkDate() function like this

=FirstWorkDate(HIST_DATE - 1, 1)

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/firstworkdate.htm

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Thu, 08 Mar 2018 14:20:48 GMT

If I`m using FirstWorkDate(HIST_DATE - 1, 1) in script , it will bring me values from two day ago .

My table contains only Date`s with values , because I excluded those days which are considered holdays.

Ex : I have values for 27-Feb, 26-Feb, 24-Feb, 23-Feb. If I select 27-Feb it brings me for PD values from 26-Feb , but if I select 26-Feb it brings for PD values from 23-Feb then if I select 24-Feb it will also bring values for 23-Feb.

Re: Show all DEPARTMENT`s even if value is null

sunny_talwar — Thu, 08 Mar 2018 14:26:26 GMT

Ex : I have values for 27-Feb, 26-Feb, 24-Feb, 23-Feb. If I select 27-Feb it brings me for PD values from 26-Feb , but if I select 26-Feb it brings for PD values from 23-Feb then if I select 24-Feb it will also bring values for 23-Feb.

Is this what is happening today or is this what you want to happen?

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Thu, 08 Mar 2018 14:29:14 GMT

This is happening. What is want is when is select 26 , to bring me values for PD from 24. And when I select 24 to bring values for PD from 23.

Re: Show all DEPARTMENT`s even if value is null

sunny_talwar — Thu, 08 Mar 2018 14:31:10 GMT

Can you share the data for 26, 24, and 23?

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Thu, 08 Mar 2018 14:33:23 GMT

I will try to send you the same file with dates from our other discussions. It`s kinda the same.

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Fri, 09 Mar 2018 06:08:04 GMT

Hey Sunny,

I`m attaching the excel file with data`s and the qvw file with the FirstWorkDate(HIST_DATE - 1, 1) function.

As you can see, for prev day (PD) values, when I select AsOfHistDate 26-Feb I have the same values like when I`m selecting 24-Feb.

But what I want is when I select 26 , in PD to have values from 24 , and when I select 24 to have values from 23.

Thank you again.

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Fri, 09 Mar 2018 06:11:00 GMT

Hy Kevin,

Thanks for your time and answer. Your solution is ok , but I don`t want to make the calculation in Table Expression , but thanks again.

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Fri, 09 Mar 2018 06:35:34 GMT

And the excel file

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Fri, 09 Mar 2018 10:01:55 GMT

Unfortunately this approach is not good if I want to calculate Average , Month to date and last month , Or is there a possibilty for these calculations

Re: Show all DEPARTMENT`s even if value is null

sunny_talwar — Fri, 09 Mar 2018 10:02:48 GMT

Not sure what your concerns are....

Re: Show all DEPARTMENT`s even if value is null

sunny_talwar — Fri, 09 Mar 2018 10:04:03 GMT

24th was a Saturday and 25th was a Sunday in Feb of 2018. When we say weekend, Qlik means Saturday and Sunday to be both as Weekends. You are working with special weekends?

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Fri, 09 Mar 2018 10:04:13 GMT

Choose please for example 26th Date. And check in PD if values are brought for 24th.

Re: Show all DEPARTMENT`s even if value is null

razvan_brais — Fri, 09 Mar 2018 10:06:35 GMT

Let`s forget about Weekends. Saturday is considered work day. In the final form , I would like to show , every time in PD , previous day with values . So if 26 is selected the previous day for which I have records is 24.