topic Re: Set analysis or mapping load in QlikView

Set analysis or mapping load

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

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 .

Re: Set analysis or mapping load

sunny_talwar — Thu, 22 Jun 2017 11:30:54 GMT

May be this

Quantity

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

Revenue

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

Re: Set analysis or mapping load

florentina_doga — Thu, 22 Jun 2017 11:57:36 GMT

pls see attach

Re: Set analysis or mapping load

madhuqliklondon — Thu, 22 Jun 2017 12:28:36 GMT

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

Re: Set analysis or mapping load

florentina_doga — Thu, 22 Jun 2017 12:36:22 GMT

I did not understand. Please exemplify me

Re: Set analysis or mapping load

madhuqliklondon — Thu, 22 Jun 2017 12:41:38 GMT

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 .

Re: Set analysis or mapping load

florentina_doga — Thu, 22 Jun 2017 12:59:26 GMT

You can attach an example

Re: Set analysis or mapping load

madhuqliklondon — Thu, 22 Jun 2017 13:01:23 GMT

Hi Sunny,

It's still coming up with zero values.

Re: Set analysis or mapping load

sunny_talwar — Thu, 22 Jun 2017 13:08:07 GMT

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

Re: Set analysis or mapping load

madhuqliklondon — Thu, 22 Jun 2017 13:15:49 GMT

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

Re: Set analysis or mapping load

madhuqliklondon — Thu, 22 Jun 2017 13:28:58 GMT

Hi Sunny,

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

Re: Set analysis or mapping load

sunny_talwar — Thu, 22 Jun 2017 13:46:28 GMT

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

Re: Set analysis or mapping load

madhuqliklondon — Thu, 22 Jun 2017 15:00:37 GMT

please find the excel file Sunny.

Re: Set analysis or mapping load

sunny_talwar — Thu, 22 Jun 2017 15:01:38 GMT

I thought you were going to attach your dashboard

Re: Set analysis or mapping load

sunny_talwar — Thu, 22 Jun 2017 15:12:46 GMT

May be like attached

Re: Set analysis or mapping load

madhuqliklondon — Mon, 26 Jun 2017 08:56:26 GMT

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.

Re: Set analysis or mapping load

sunny_talwar — Mon, 26 Jun 2017 09:04:33 GMT

Which field say free gift?

Re: Set analysis or mapping load

madhuqliklondon — Mon, 26 Jun 2017 09:24:49 GMT

Product type has got standard and free gift .

Re: Set analysis or mapping load

sunny_talwar — Mon, 26 Jun 2017 11:12:25 GMT

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

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

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