Creating a Sum based on Order Date and Days Supply

[nlaughton]

Hello  

i need some assistance creating a new field based on the initial order date, the days supply of that order and the associated Power of that Order.  I'm not sure what this kind of calculation or loop is called but here is the scenario...

Here is some data below. what i am looking to find is a total Power Value for any given day. Table 1 is data i have, Table 2 shows kind of what i am looking for, i do not really need a table with every date, more the ability to indicate the highest power for any Customer from any date and what the power is for a customer on a selected date, ie Max Power for Customer 1255 = 29 and  Total Power for Customer 1255 on 12/12/19 is 25.  

Table 1

Customer	Order Date	Days Supply	Power
1255	12/5/2019	10	10
1255	10-Dec	12	15
1255	12/18/2019	8	14
1366	12/6/2019	8	12
1366	7-Dec	3	30
1366	12/17/2019	10	5

Table 2

Date	Customer	Power			Total Power
12/5/2019	1255	10			10
12/6/2019	1255	10			10
12/7/2019	1255	10			10
12/8/2019	1255	10			10
12/9/2019	1255	10			10
12/10/2019	1255	10			10
12/11/2019	1255	10			10
12/12/2019	1255	10	15		25
12/13/2019	1255	10	15		25
12/14/2019	1255	10	15		25
12/15/2019	1255	10	15		25
12/16/2019	1255		15		15
12/17/2019	1255		15		15
12/18/2019	1255		15	14	29
12/19/2019	1255		15	14	29
12/20/2019	1255		15	14	29
12/21/2019	1255		15	14	29
12/22/2019	1255		15	14	29
12/23/2019	1255			14	14
12/24/2019	1255			14	14
12/25/2019	1255			14	14
12/26/2019	1255			14	14

[Marcos_rv]

try this: create a simple table with the dimensions customer, date and with the following metric: sum (total Power) Regards !!!

[nlaughton]

thanks for your reply...i don't have the value yet for max power that i put in table 2, that was more to show what i was looking for. 

i'm thinking i need some kind temporary table in the script with a loop...but i'm not exactly sure. 

[Marcos_rv]

sorry, try this:

Sum (total <date>  Power ) //  TOTAL POWER

SALUDOS!!!

[Marcos_rv]

Qlik uses the associative model, which makes all the calculations to be displayed according to the dimensions contained in the graph, automatically groups them according to the fields or dimensions, in this case you have power per day, but you want to have as day and the total for a client, with which it is convenient for you to use the TOTAL <> statement and pass the Date parameter, this makes it group but ignore the date, therefore it will group by customer.

In the case of having as a Power dimension, you should also add it into the total, it would look like this: Sum (TOTAL <Date, Power> Power).

Regards!!!!

[Brett_Bleess]

Did Marcos' posts help you get things resolved?  If so, do not forget to return to the post and use the Accept as Solution button on the one(s) that were most helpful to your situation.  If you did something different, consider posting that and then mark that as the solution, otherwise leave an update for us.  

That being said, you can also utilize the Design Blog area of Community to search for examples, there are over 700 posts in this area from our internal experts and most are how-to related, so you will likely find it pretty useful.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett