Mutation to stock chart

[#1]

Dear Qlik community,

my question is probably not difficult at all, however I don't really know what to search for.

My goal is to create a chart that displays the amount of stock per date. I have no actual amount of stock per product per day available, only mutations.

So, for example:

Product ID	Date	Mutation
1000	01-6-2016	+ 10
1000	02-6-2016	- 1
1000	03-6-2016	- 2
1000	03-6-2016	+ 5
1000	05-6-2016	-4
1001	03-6-2016	+ 200
1001	04-6-2016	-25
...	...	...

What i would like is to be able to select for example 05-6-2016 and get 8 for Product ID 1000 and 175 for Product ID 1001.
I can achieve this for any specific date with set analys like this:

=SUM({1<Date ={"<=$(=today())"}>}[Mutation])

However, i want to be able to use date as a dimension, and get the sum of all mutations from the start until the specific date.

So, using my example, the result should be:

Product ID	Date	Stock
1000	01-6-2016	10
1000	02-6-2016	9
1000	03-6-2016	7
1000	03-6-2016	12
1000	05-6-2016	8
1001	01-6-2016	0
1001	02-6-2016	0
1001	03-6-2016	200
1001	04-6-2016	175
1001	05-6-2016	175

Thanks in advance!

[sunny_talwar]

Try like this:

RangeSum(Above(Sum({<Date>}Stock), 0, RowNo())) * Avg(1)

Sample to follow...

[sunny_talwar]

Not sure I completely understand the raw data, but I used this:

Table:

LOAD * INLINE [

    Product ID, Date, Stock

    1000, 01-6-2016, 10

    1000, 02-6-2016, -1

    1000, 03-6-2016, -2

    1000, 04-6-2016, 5

    1000, 05-6-2016, -4

    1001, 01-6-2016, 0

    1001, 02-6-2016, 0

    1001, 03-6-2016, 200

    1001, 04-6-2016, -25

   1001, 05-6-2016, 0

];

to get this:

With Selection:

Expression:

If(RangeSum(Above(Sum({<Date>}Stock), 0, RowNo())) = 0, 0.00001, RangeSum(Above(Sum({<Date>}Stock), 0, RowNo()))) * Avg(1)

[#4]

Hi Sunny T,

thank you for your help and suggested solution. Basically you've done exactly what i need (sorry for the 04-6-2016 typo in base data by the way) except i dont have the 0 values that you've added in the load.

Because of this, if we still use the example, for product 1001, 05-5-2016 won't be shown.
Also, if i add more data (say muation values for 4 years), and i make a selection like year = 2015, the stock amount of

10-5-2015 is different to the same date's stock amount when i dont select this year, probably because the start date is now 01-1-2015.

So, thanks again, im almost there now

[sunny_talwar]

Issue 1 can only be resolved if you have all dates for all your Product ID. If the date is not available for each Product ID, you won't be able to see it.

Issue 2 is simple, you can add all your date related fields to the set analysis:

If(RangeSum(Above(Sum({<Date, Month, Year, MonthYear, Quarter, QuarterYear>}Stock), 0, RowNo())) = 0, 0.00001, RangeSum(Above(Sum({<Date, Month, Year, MonthYear, Quarter, QuarterYear>}Stock), 0, RowNo()))) * Avg(1)

[#6]

Hi Sunny T,

thanks again for your reply. So, if u fully want to achieve as described above, i basically need to adjust the backend. If it's the only possibility thats exactly what we'll do, thanks!

The second issue is indeed solved with you're suggestion, so that's nice!

Is it also possible to use set analysis or other selections within RangeSum(Above(Sum({<Date>}Stock), 0, RowNo())) * Avg(1) ?

So, instead of using product, use a related dimension like supplier or segment? Because this doesnt seem to work as id expect.

And why is the expression total 0 for this expression?

Regards,

Jeroen

[sunny_talwar · Accepted solution]

So, if u fully want to achieve as described above, i basically need to adjust the backend. If it's the only possibility thats exactly what we'll do, thanks!

Yes AFAIK this is might be the only efficient solution. The other one might be synthetic dimensions using ValueList or using Island Dimension which can get very messy.

Is it also possible to use set analysis or other selections within RangeSum(Above(Sum({<Date>}Stock), 0, RowNo())) * Avg(1) ?

Yes you can use set analysis within the Sum function as you would normally do. You might have to apply the same set analysis to the Avg() function

So, instead of using product, use a related dimension like supplier or segment? Because this doesnt seem to work as id expect.

This was based on the sample you provided. If dimension is Supplier or Segment, it should accumulate based on those dimensions.

And why is the expression total 0 for this expression?

What total would you want to see? the final accumulated value? Sum of all the rows? You can use Dimensionality() function to get what you are looking for. or you can use Aggr() function. Let me know what you want and I can give you a more precise expression which will get you the required total as well

[#8]

Hi Sunny,

thank you so much for your help. I know what to do now, change our backend. And while i wait for this to happen, i can use your example to show users almost everything they want.

I don't really know what answer to accept because they are all true

Thanks a lot!