Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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):
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
Hi,
can use an IF together with Soma, would that solve?
Sum(
IF(
IsNull(Date_deleted) = false() and Date_deleted < Load_date,
Value
)
)
regards
Hi Yoshida,
Thanks for your reaction,
Let me find out and I will get back to you.
kr
Martijn
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.
=sum(aggr(Value, Load_Date, Item))
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
];
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
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
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
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