Percentage against total of selected

[DanielReed]

hi, i have a dateset from April 2019 to March 2020. On loading the data, i realised i had a few rows of data with dates before April 2019 and after March 2020. 

Firstly, how can i define the selection - April 2019 to March 2020 in the script?

What i am then trying to do is show the % are picked on which day. I have selected only the dates i am interested in and locked that selection. I have then used a straight table with the following expression.

 sum([Quantity Ordered])/sum(TOTAL [Quantity Ordered])

This gives me the answer i am expecting, however when i make a further selection the % changes - i want it to remain as the % of the total selected. 

I then fix that issue by adding the below to the expression

sum([Quantity Ordered])/sum(TOTAL {1} [Quantity Ordered])

However, as i have previously selected April 2019 to March 2020, this now gives the % against the total of the selected - 99%

How can i change this expression to only display the % against my selected?

Hope that is clear, thanks in advance.

Dan

[vamsee · Accepted solution]

Answer to your first question, you can limit dates by using the following

/***** Limit Dates ****/
Let vStartDateNum =Num(Date(Date#('04012019', 'MMDDYYYY'))); 
Let vEndDateNum =Num(Date(Date#('03312019', 'MMDDYYYY')));
Dates_Limit_Table:
NoConcatenate
Load 
	Num(Floor(Num($(#vStartDateNum)+RowNo()))) as Dates_Limit
AutoGenerate ($(vEndDateNum)-$(vStartDateNum))
;

And while loading your table use a simple exists function like

Where 
	Exists(Dates_Limit, Num(Date_Field))

I assume that 1% is the other dates which are excluded from the set but included in the Denominator

I would also add "Date_Field=" in the denominator to exclude any date selections, just to be sure

Like

sum(TOTAL {1<Date_Field=>} [Quantity Ordered])