topic Re: Total sales in QlikView

Total sales

Wed, 27 Jan 2016 10:03:16 GMT

I have Unit number , Date, Sales fields, I would like to calculated sum of sales for each unit number , from the minimum date of each department number to select no of months.

ex: I have Unit 1 in the year 2004/jan/25 sale $100, for the same Unit on 2004/feb/21 I have sale say $200 and for the

Unit 2 in the year 2006/<mar/25 sale $50, for the same Unit on 2007/Apr/19 I have sale say $250

now I would like to calculate a field on Date which will give me First 2Month, 4monts, 6Month, .....>24Month.

if I select on first 2Months I would like to see

for Unit number 1 Sale 300

for Unit number 2 Sale 50

Can any one help on this.

Please find the sample data on attachment.

Thanks

John

Re: Total sales

sunny_talwar — Wed, 27 Jan 2016 12:21:35 GMT

I did not see any reference to Departmen number in your attached Excel file. Is that the same thing as Unit Number?

Re: Total sales

Wed, 27 Jan 2016 12:23:39 GMT

Sorry for my mistake, yes your correct that is Unit Number.

Re: Total sales

sunny_talwar — Wed, 27 Jan 2016 12:27:25 GMT

Another questions, I don't see Unit Number 1 and 2. I see them starting from 6 and the next one is 7511. Also you want to see sales for Dec-2015 and Jan-2016 when you are looking for a 2-Month view?

Re: Total sales

Wed, 27 Jan 2016 12:35:34 GMT

Thanks sunny for your response,

I have provided different test data in the excel, however to describe the problem I have taken two units as dummy data and provided expected result.

As per the Excel

For Unit 7511 for 2 Month View expected result would be 34808

For Unit 12420 for 2 Month View expected result would be 116.

Thanks.

John

Re: Total sales

sunny_talwar — Wed, 27 Jan 2016 12:41:19 GMT

Oh okay. That makes sense. Now the question still remains, which months would you want to see for First 2 month view? Dec-2015 and Jan-2016?

Re: Total sales

Wed, 27 Jan 2016 12:56:47 GMT

No Sunny , the requirement is

I need a filter which contains 2Month, 4months, 6 Months.....>48Months as values, when i click on 2Months it has two show the first 2 months data from its minimum date [minimum date is different for each Unit].

for 7511 Sep, Oct 2014

for 12420 Dec 2013, Jan 2014.

Hope its clear, if not please let me know.

Re: Total sales

sunny_talwar — Wed, 27 Jan 2016 13:12:12 GMT

Check out the attached application. I created flag in the script to determine the First 2, 4, 6, 24 months and then I am using an inline table for selection.

Re: Total sales

Wed, 27 Jan 2016 13:20:51 GMT

No Sunny Wrong results.

for the Months view

For Unit 7511 for 2 Month View expected result would be 34808

For Unit 12420 for 2 Month View expected result would be 116.

Thanks for the Response.

Re: Total sales

sunny_talwar — Wed, 27 Jan 2016 13:52:13 GMT

For Unit 12420, I am seeing this:

How is it going to Sum to 4?

Re: Total sales

Wed, 27 Jan 2016 13:55:37 GMT

Yes Your Correct, sum would be 116.

Re: Total sales

sunny_talwar — Wed, 27 Jan 2016 13:59:07 GMT

This?

Re: Total sales

Wed, 27 Jan 2016 14:10:03 GMT

Yes Sunny ,it seems working, however this functionality I want to implement with out these flags, just I need one field which contains first 1 Month, first 2 Months ..........up to today whatever the month it is, ideallyy autonumber(MonthName(Date),'MN');

I want to calculate a field which gives me a distinct number for each month through out all the years up to today for all the units.

then if I click on that field I would like to see those many months of data for each unit from its minimum date.

Re: Total sales

PradeepReddy — Wed, 27 Jan 2016 14:32:15 GMT

see the attachment, it might be helpful for you.... it is similar to sunindia‌

I have add crosstable, to convert all the period fields into into one field.

Re: Total sales

sunny_talwar — Wed, 27 Jan 2016 16:33:22 GMT

Another way:

Re: Total sales

sasikanth — Thu, 28 Jan 2016 03:48:17 GMT

HI,

Try this

Expression,

=sum(if(Date>=MonthStart(mindate)and Date<=MonthEnd(AddMonths(mindate, vMax)), Sales))

PFA for details

Re: Total sales

Thu, 28 Jan 2016 04:50:36 GMT

Sunny in the above attachment 1st Month Results are not populating. also there are some Year month values also missing .

here I am attaching a work around ,please find it , there is a bug on it I am getting values individually for every period, however when we are not selecting the period the amounts are showing huge for every unit. also please kingly let me know can I do it in more optimal way? actually the file size is 2.7 MB but now it has moved to 77 MB so some where I did mistake please kindly provide me a thought.

swuehl‌

tresesco‌maxgro ‌

hic‌

Thanks

John

Re: Total sales

sunny_talwar — Thu, 28 Jan 2016 10:06:28 GMT

I see that you are using CrossTable() in the script. What exactly are you trying to do there? Are you using the solution proposed by Pradeep? He might be able to help you better if you are using his way of doing it?

Re: Total sales

sunny_talwar — Thu, 28 Jan 2016 10:47:10 GMT

You had 435,278 observations in your Excel File and after your script ended the number of observation increased to 33,081,128 (which equals the number of observations * No. of Period you have created). This is the reason the file size increased from 2.7 MB to 77 MB here.

Re: Total sales

Thu, 28 Jan 2016 10:57:37 GMT

Yes , there is the problem.