topic Re: Sales categeory in QlikView

Sales categeory

kakani87 — Wed, 06 Jun 2018 09:10:39 GMT

Hi Folks ...

I'm having sales data customer and item wise.

Now i want to display three category's

1.New Sale (There should not be sale for that customer with in 365 days from max saledate)

2.ASC (only shname=ASC ) I'm taking exp as Sum({<shname={'ASC'}>}sale)

3.item <> ASC and <>New sale to be considered as existing sale

PFA.

Kakani.

tresesco‌

stalwar1‌

Re: Sales categeory

sunny_talwar — Wed, 06 Jun 2018 09:14:38 GMT

Where are you looking to display this? In a text box object?

Re: Sales categeory

kakani87 — Wed, 06 Jun 2018 09:20:47 GMT

No sunny , In a pivot table

Re: Sales categeory

kakani87 — Wed, 06 Jun 2018 10:37:30 GMT

Hello Sunny Any help ?

Re: Sales categeory

sunny_talwar — Wed, 06 Jun 2018 11:09:00 GMT

What is going to be the expression? Overall sales?.. I am focusing on req 1 right now

Re: Sales categeory

kakani87 — Wed, 06 Jun 2018 11:49:29 GMT

Yes over all sales.

Actually we need to show the first requirement for new sales like from the customer max date of transaction/invoice to previous in 365 days there should not be any transaction then that sale should be considered as new sale

In the above posted sample data the new sale to be considered for cust02 i.e 100000

Re: Sales categeory

sunny_talwar — Wed, 06 Jun 2018 12:09:35 GMT

Oh you mean that there should not be any sale prior to the last 365 days?

May be this

=Sum({<Cust = {"=Sum({<Dt = {[$(='<=' & Date(Max(Dt)-365))]}>} sale) + Sum({1} 0) = 0"}>}sale)

But Cust01 also doesn't have sale prior to 365 days... why is he not included?

Re: Sales categeory

kakani87 — Thu, 07 Jun 2018 10:33:09 GMT

Hi Sunny ....

Let me correct what yesterday I shared you

In 365 days from as on date Min transaction date for a customer to be considered as New Sale , if the other transaction or invoice dates after the min date to be considered as Existing sale

In the latest shared document for cust01 02-04-2018 'dd/mm/yyyy' sale as new sale, 5/6/2018 as existing sale

for Cust02 10/6/2018 sale happened as Newsale,

for Cust03 30/7/2017 sale as newsale and for (20/8/2017 & 16/5/2018) sale as existing sale to be considered

with shname=ASC excluded from this

i.e sum({<shname-='ASC'>}sale) as ASC sale.

Kindly look into this

Re: Sales categeory

sunny_talwar — Thu, 07 Jun 2018 10:52:36 GMT

What is the expected output?

Re: Sales categeory

kakani87 — Thu, 07 Jun 2018 11:11:14 GMT

I'm building a costing dashboard

In that one requirement is to show New & Existing sale , ASC(Annual support charges) to be displayed customer , item/product wise

--->Sale happened in 365 days from as on date to consider

1 & 2. If sale happened with a customer in 365 days then min date sale to be considered as New sale and After that date all other transactions happened to be Considered as Existing sale with excluded shname = 'ASC' (This will be shown separately as ASC)

3. Where the ASC to be considered for only shname = 'ASC' sale I'm considering as below

sum({<shname='ASC'>}sale) as ASC

> New Sale

> Existing Sale

> ASC

Am i able to explain what need to be displayed

Re: Sales categeory

sunny_talwar — Thu, 07 Jun 2018 11:20:01 GMT

I am still not certain I understand. Can you put the expected output in an Excel file with the exact numbers you are hoping to see?

Re: Sales categeory

kakani87 — Thu, 07 Jun 2018 11:54:49 GMT

Refer sheet 2 for Expected output

This is sample data , I will let you know one thing that need to consider 365 from as on date and display

> sale happened customer with in 365 min date as New Sale

> After that min date tarnsactions to be considered as Existing sale

For instance sale happened for cust01 first on 02/04/2018 (New sale) but after that again sales done on 5/6/2018 to be considered as Existing sale like so on for al the customers

With in this shname='ASC' to be excluded for New and existing sale

Re: Sales categeory

kakani87 — Fri, 08 Jun 2018 06:58:19 GMT

Sunny ... Any progress on this

Re: Sales categeory

sunny_talwar — Fri, 08 Jun 2018 09:00:38 GMT

To tell you the truth, I am not sure I understand what you want. Unless I understand, I am not sure how will I be able to help.

Re: Sales categeory

kakani87 — Fri, 08 Jun 2018 09:54:13 GMT

In sales data having with customer wise , item wise

Now I want to show three measures

1.New Sale

2.Existing sale

3.ASC (Annual support charge)

1. For New sale requirement I want to consider sale happened with a customer in last 365 days

i.e

Date	customer	item	value
02-04-2108	Cust01	IG0003	15000
05-05-2018	Cust01	IG0004	5000
07-06-2018	Cust01	IG0003	1000

Here displayed transactions happened on different days

Now i want to display first record as new sale for Cust01 (With in 365 days from as on date let N # of sale happen with a customer but Min date or first date to be considered as NewSale )

02-04-2108

Cust01

IG0003

15000

2. Existing Sale : -

As in the above New sale Min date to be considered but here, after that Min date transactions to be considered as Existing sale

05-05-2018	Cust01	IG0004	5000
10-06-2018	Cust01	IG0003	1000

Re: Sales categeory

kakani87 — Fri, 08 Jun 2018 10:32:28 GMT

One simple example

You are my customer and i sold you services or products 3 times in last 365 days

Date	cust	item	ShName	value
30/06/2017	Sunny	IG0001	Implementation	10000
10/03/2018	Sunny	IG0003	PA WOW(Upgrade)	5000
01/05/2018	Sunny	IG0001	ASC(Annual Support charges)	2000

All the 3 sale may be for same or different Items this is for explaination purpose

1. [New Sale] - [Shname(ASC)]

in this New Sale considered as

30/06/2017

Sunny

IG0001

Implementation

10000

2.[Existing Sale] - [Shname(ASC)]

Existing sale is

10/03/2018

Sunny

IG0003

PA WOW(Upgrade)

5000

3.[ASC] = sum({<ShName={'ASC'}>}value)

ASC

01/05/2018

Sunny

IG0001

ASC

2000

Re: Sales categeory

sunny_talwar — Fri, 08 Jun 2018 11:08:04 GMT

Will check

Re: Sales categeory

kakani87 — Fri, 08 Jun 2018 11:11:54 GMT

Thanks a lot for responding to the query.

Hope I'm able to explain you the requirement properly atleast now

Re: Sales categeory

kakani87 — Tue, 12 Jun 2018 04:16:04 GMT

Dear Sunny .... Could you check with this once

Regards,

Sai.