accumulated amounts problem

[#1]

Hi All,

I have attached the sample data in which i have to first figure out the actual sale of each company of each date.

Each company has different stores and we have the amounts numbers at the end of each hour for each store. so for any particular date we can subtract the amounts of maximum hour e.g. 3 with the minimum hours, e.g 1. and when we have all the amounts store wise then we can sum them up to have the amounts at company wise. and we need the simple bar chart showing each company and date wise amounts.

Need your input on how to first build that data as, i guess, showing this in graph would be simple if i manage to build data.

[#2 · Accepted solution]

Skhan,

try to load data wit this script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='YYYY-M-D';

SET TimestampFormat='YYYY-M-D h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

tab1:

LOAD company,

     store,

     date,

     time,

     [accumulated Sale]

FROM

[http://community.qlik.com/servlet/JiveServlet/download/540108-109224/accumulated%20sale.xlsx]

(ooxml, embedded labels, table is Sheet1) ;

tab2:

load company,

     store,

     date,

     time,

     if (company&'#'&store=Previous(company&'#'&store), [accumulated Sale]-Previous([accumulated Sale]),[accumulated Sale]) as sale

     Resident tab1 Order by  company, store, date, time;

     drop table tab1;

regards

D