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.

[sunny_talwar]

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

[razvan_brais]

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

[razvan_brais]

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.

[razvan_brais]

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.

[razvan_brais]

And the excel file

[razvan_brais]

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

[sunny_talwar]

Not sure what your concerns are....

[sunny_talwar]

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?

[razvan_brais]

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

[razvan_brais]

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.