Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: multiply a measure outcome with other data

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

roisolberg

Creator

2021-10-11
02:54 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

multiply a measure outcome with other data

Hi,

a little bit of background:

i have an object-Price Quotes (PQ), some of them are regular and some of them are with "steps" - few lines for the same part with different prices - and the customer will usually order only one of the lines.

(qty = 1 - price =x / qty=3 - price=y /...)

the first measure i've created is the potential of the PQ's (sum of all regular PQs + the maximum potential of the stepped ones)

so i've created a flag in the script weather a line should be calculated as potential or not - "Potential_Calculation_Flag"

and i've calculated the potential for open quotes:

sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen={'Open'}>}PriceQuote_Line_TotalPriceUSD)

after the potentail was found, i had to find the realization percentage so i calculated the linked orders value and divided by the potentail

percentage:

sum(PQ_SalesOrder_Value)/sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen-={'Open'}>}PriceQuote_Line_TotalPriceUSD)

Sum:

sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen={'Open'}>}PriceQuote_Line_TotalPriceUSD)*(sum(PQ_SalesOrder_Value)/sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen-={'Open'}>}PriceQuote_Line_TotalPriceUSD))

now my question is:

i went and check how long did it take to win a PQ (Order Date - PQ Date), and created a percentage for each month

(PriceQuote_SalesOrderDate-PriceQuote_Date)/30 as PQ_To_Order_Months_tmp

now my question is:

i want to multiply the sum of realization with the percentage and get the expected realization for each coming month

sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen={'Open'}>}PriceQuote_Line_TotalPriceUSD)*(sum(PQ_SalesOrder_Value)/sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen-={'Open'}>}PriceQuote_Line_TotalPriceUSD))

*

Sum(Percentage)

but this doesn't seem to work, not in a table and not in a chart (

how can i do it?

the monthly percentage is linked to the Price quotes with the PQ_To_Order_Months

i've tried to do it with and without a join

any ideas?

Many Thanks,

Roi

306 Views

1 Solution

Accepted Solutions

roisolberg

Creator

2021-10-14
04:51 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Issue solved.

i've disconnected the PQ_To_Order_Months from the PQ table and created it as a stand alone table

with Months | Percentage

Thanks! 🙂

4 Replies

roisolberg

Creator

2021-10-13
09:12 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

anyone? 😏

244 Views

stevejoyce

Specialist II

2021-10-13
09:33 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hard for me to follow without actually working in your app.

Can you make 3 seperate expressions in a table with only PQ_To_Order_Months as dimension, and see what the results are for each piece:

1)

sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen={'Open'}>}PriceQuote_Line_TotalPriceUSD)

2)

(sum(PQ_SalesOrder_Value)/sum({<Potential_Calculation_Flag={1},PriceQuote_Flag_WinLossOpen-={'Open'}>}PriceQuote_Line_TotalPriceUSD))

3)

Sum(Percentage)

228 Views

roisolberg

Creator

2021-10-14
02:23 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@stevejoyce Thank you for your replay!

and thank you for setting my mind straight, first thing of course is it separate the measures,

once i did it, i realized that the first expression is calculated only for **Open** PQ's while the PQ_To_Order_Months is for closed PQ's 😕

how do i combine the two?

i need to get the total realization (that can be changed according to filters) and multiply it by the percentage for each month that came from the closed PQ's

194 Views

roisolberg

Creator

2021-10-14
04:51 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Issue solved.

i've disconnected the PQ_To_Order_Months from the PQ table and created it as a stand alone table

with Months | Percentage

Thanks! 🙂