topic Re: Last value of a dimension as a condition in App Development

Last value of a dimension as a condition

gitguto — Sat, 16 Nov 2024 00:32:08 GMT

Hey folks,

I have three columns "Version", "Units sold" and "Date". I need to find an expression that is able to:

sum(units sold in 2021 from the historical version + units sold in 2021 from the latest version available).

I can't find a solution because the version name changes every week from w1 to w2, w3... and they don't have fixed starting dates associated with them. (Ex. w01 doesn't necessarily starts from 1/1/2021, w2 doesn't necessarily starts from 1/8/2021, etc)

Example

VERSION	UNITS SOLD	Date
HISTORICAL	10	12/1/2020
HISTORICAL	20	1/1/2021
w01	10	1/1/2021
w01	5	1/1/2021
w02	15	1/1/2021
w02	15	1/8/2021
w05	30	1/15/2021

Expected output = 20 + 30 = 50

I tried MaxString, but it returns "HISTORICAL" instead of the latest W#

Sum({<VERSION = {'$(=Maxstring([VERSION]))'} , Date = {">=$(=YearStart(max(Date)))"}>} [Units Sold]) +
Sum({<VERSION = {'HISTORICAL'} , Date = {">=$(=YearStart(max(Date)))"}>} [Units Sold])

P.S. I'm writing this expression in the "Edit Expression" field inside a bar chart ->Data - Measure(Length of bars)

Re: Last value of a dimension as a condition

Saravanan_Desingh — Thu, 25 Feb 2021 04:03:02 GMT

Try this,

=Sum({<VERSION = {'HISTORICAL'}, Date = {">=$(=YearStart(max(Date)))"}>} [UNITS SOLD]) =Sum({<VERSION = {"w*"}, Date={">=$(=max(Date))"}>} [UNITS SOLD])

Re: Last value of a dimension as a condition

gitguto — Thu, 25 Feb 2021 14:45:02 GMT

Thanks for the reply,

Unfortunately this does not work because I need to sum all the values from the latest version from 2021, not only the entry with the maximum Date.

I'll give you another example:

In this case, my expected output would be 20 + 10 + 25 + 30 = 85. That is (20 +10 = (all units sold in 2021 from the historical version) + 25 +30 = (all units sold in 2021 from the latest version available, in this example it would be w04)

Re: Last value of a dimension as a condition

salonicdk28 — Thu, 25 Feb 2021 15:18:30 GMT

Please try this-

Instead of single quote, try with double quotes as mine is working , refer below-

=sum({<VERSION={"$(=MaxString(VERSION))"},Date={">=$(=Yearstart(max(Date)))"}>}[UNITS SOLD])+
sum({<VERSION={'HISTORICAL'},Date={">=$(=Yearstart(max(Date)))"}>}[UNITS SOLD])

Re: Last value of a dimension as a condition

gitguto — Thu, 25 Feb 2021 15:36:10 GMT

Thank you!! It works!!

In the same thread, would you know how to fix the Year to 2021? I'm afraid this Yearstart(max(Date)) condition will fail eventually when I start getting data from 2022. I tried something like:

Date={"$(=Year(Date) = 2021))"} but it returns 0

Re: Last value of a dimension as a condition

gitguto — Thu, 25 Feb 2021 15:48:29 GMT

Nevermind, I got it!

Sum({<VERSION = {'HISTORICAL'} , Date = {"*2021*"}>} [Parts and Accessories])
+Sum({<VERSION={"$(=MaxString(VERSION))"}, Date = {"*2021*"}>} [Parts and Accessories])