Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])