Show all DEPARTMENT`s even if value is null

[razvan_brais]

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.

[kevinalvino]

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

[sunny_talwar · Accepted solution]

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;

[razvan_brais]

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

[sunny_talwar]

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

[razvan_brais]

Weekend or Holiday.

[sunny_talwar]

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/f...

[razvan_brais]

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.

[sunny_talwar]

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?

[razvan_brais]

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.