Cumulative graph based on different dates

[Martijn1]

Hello,

I want to show a cumulative result in a graph over time, however I can't manage it. I hope you guys know a way.

Table 1: Data

Graph 1: Cumulative result

The data (See attached picture):

• Item: Unique ID
• Load_date: The moment at which the data was uploaded (so from 1-1-2019 until 1-8-2019 I uploaded the data monthly, which is a dump not a delta)
• Status: Tells something about the item and is a derived column. Prognosed, means that the item will be deleted in the future (based on the moment of Load_date)
• Date_deleted: Date at which the item will be deleted or is deleted.
• Value: The value corresponding with the item

The problem I face is the following:

The prognosed values are values which I want to add up at the moment which they are expected to be deleted, not at the moment at which they get the status of "Prognosed".  To do so, I need to combine the 2 dates I guess (Load_date and Date_deleted).  But I have no idea how i do that, so that I still can use it as a timeline (x-axis) in my graph.

Do I need a master calendar table for that? Or is there a different solution?

Thanks

[Yoshidaqlik]

Hi,

can use an IF together with Soma, would that solve?

Sum(
	IF(
    	IsNull(Date_deleted) = false() and Date_deleted < Load_date,
        Value
    )
)

regards

[Martijn1]

Hi Yoshida,

Thanks for your reaction,

Let me find out and I will get back to you.

kr

Martijn

[Martijn1]

Hi Yoshidaqlik, 

I don't think this will work, because you will end up with a timeline, which ends at 1-8-2019.

Just in case , I added another picture of the situation. Maybe this picture will clarify things better.

I hope any one can help me out here.

[lanlizgu]

=sum(aggr(Value, Load_Date, Item))

[lanlizgu]

If you want to sum just the data for the Deleted Status try the following

=sum({<Status={"Deleted"}>} aggr(Value, Date, Item, Status))

I checked with the following data and is working correctly

Table1:
LOAD * INLINE [
Item, Date, DeletedDate, Status, Value
1, 01/09/2019, 25/04/2019, Deleted, 100
1, 01/10/2019, 25/04/2019, Deleted, 100
1, 01/11/2019, 25/04/2019, Deleted, 100
1, 01/12/2019, 25/04/2019, Deleted, 100
1, 01/01/2020, 25/04/2019, Deleted, 100
1, 01/01/2020, 25/04/2019, Existing, 100
2, 01/01/2020, 31/12/2019, Deleted, 50
2, 01/01/2020, 31/12/2019, Existing, 45
];

[Martijn1]

Ha Lanlizgu,

thanks but this will not work. You see, the grey records (so the records with Load_date 1-9-2019 up to 1-1-2020) doesn't exist yet. I only know when the item will be deleted based on the deleted date.

KR

Martijn

[Yoshidaqlik]

Create a loop where you will create a table with dates separate from other tables,
This will create every month until future dates so you can make this chart

Set a=1;

Let b= diff_month_min_max

Do while a<diff_month_min_max

    LOAD monthstart(min_date,$(a)) as new_date from autogenerate 1;

    Let a=a+1;

Loop

[Martijn1]

Hi Yoshidaqlik,

I'm pritty new to qlik sense.

If I make this table with the new_date as outcome I guess I should connect the Date_deleted field to this date field, right? 

And based on your code in your previous message, I then create the Graph.

Am I right?

KR 

Martijn

[Yoshidaqlik]

I thought about creating a table without connection.

This date table would be just to make the graph same as you showed in the image.

this date from the separate table would test all dates from the other table with information

that would give the effect of

SUMIFS