Solved: Set analysis or mapping load - Qlik Community

madhuqliklondon · ‎2017-06-22

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 .

sunny_talwar · ‎2017-06-26

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))

View solution in original post

sunny_talwar · ‎2017-06-22

May be this

Quantity

=Count(DISTINCT {<Value= {'0'}>} [Order number])

Revenue

=Sum(DISTINCT {<[Order number] = P({<Value= {'0'}>} [Order number])>} Value)

florentina_doga · ‎2017-06-22

pls see attach

madhuqliklondon · ‎2017-06-22

florentina.dogaru‌ I cannot create gift and order_gift as this is in dashboard. Please advice.

florentina_doga · ‎2017-06-22

I did not understand. Please exemplify me

madhuqliklondon · ‎2017-06-22

florentina.dogaru In your example you have created gift and order_gift columns right by resident load. I am unable to do like that. Is there a way without creating new columns .

florentina_doga · ‎2017-06-22

You can attach an example

madhuqliklondon · ‎2017-06-22

Hi Sunny,

It's still coming up with zero values.

sunny_talwar · ‎2017-06-22

Would you be able to share your data in an Excel file?

madhuqliklondon · ‎2017-06-22

florentina.dogaru‌ can we able to achieve results with only below table.