Set analysis or mapping load

[madhuqliklondon]

Hi Friends,

I am  trying to generate a report where I want to get total sale values and quantities .

explanation:

When customers buy expensive products they get free gift , so items are two types standard and free gift . When they buy standard item they get free gift but not all the customers are eligible for it. In database they are hard codes as standard =0 and free gift as =1.

I have created a straight table, taken [order date], [order number],[order value] as dimensions.

From master calendar selected fields in to multi box as year, month and week and Product type into another multi box.

When I select required dates and product type as [standard] I am getting all orders without free gift as well. When I select product type as [free gift] I am only getting free gift item with zero values as they are free.

Aim 1: I would like to get only orders with free gifts and the total of that order.

Aim 2: I want to count each count of free gift to find out top 10.

All the fields are in brackets.

below is simple example .Hope this make sense. Thank you .

[madhuqliklondon]

Hi Sunny,

It is dashboard, what do you like to see in excel.

[sunny_talwar]

Share the dashboard then ... The reason I asked for Excel was to get the raw data to create a sample app.. But if you are willing to share your dashboard, then nothing can be better than that

[madhuqliklondon]

please find the excel file Sunny.

[sunny_talwar]

I thought you were going to attach your dashboard

[sunny_talwar]

May be like attached

[madhuqliklondon]

Hi Sunny, It is showing values when there is no filter, please add select field and click on free gift , the values are becoming zero again. could you please check.

[sunny_talwar]

Which field say free gift?

[madhuqliklondon]

Product type has got standard and  free gift .

[sunny_talwar · Accepted solution]

It seems that not all Amount = 0 are free gift? Is that true? I am not completely sure, but may be one of these

1)

=Sum({<Amount= {'0'}>}Aggr(Sum(TOTAL <[order guid]> {<[order guid] = P({<Amount= {'0'}>} [order guid]), Product_Type>} Amount), [order guid], SKU))

2)

=Sum({<Product_Type= {'Free_Gift'}>}Aggr(Sum(TOTAL <[order guid]> {<[order guid] = P({<Product_Type= {'Free_Gift'}>} [order guid]), Product_Type>} Amount), [order guid], SKU))