Sales categeory

[kakani87]

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‌

[sunny_talwar]

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

[kakani87]

No sunny , In a pivot table

[kakani87]

Hello Sunny Any help ?

[sunny_talwar]

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

[kakani87]

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

[sunny_talwar]

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?

[kakani87]

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

[sunny_talwar]

What is the expected output?

[kakani87]

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