Aggregate fields in a dimension

[Josef1]

Hi 

I have a question about aggregating field in a  dimension.

The data i'm loading does not have dates but it has two fields/dimensions, one for year and one for months. Both are in text format.

What i'm trying to do is to aggregate the volumes by month and compare to previous year and show the depelopment in percent on a chart.

For example, jan volumes from this year compared to jan from previous year, then jan+feb volumes from this year compared to jan+feb from previous year, so every month from Jan and forward should include the volumes from previous months. As i meantioned above the chart should show the development in percent.

So if i want to see the volumes for this year in a KPI then i have to use this syntax: Sum({<[Year]={'2020'}>}Volume)

I have tried to find a solution online for this but it doesn't seem like many have tried to do this.

Thank you in advance

[marcus_sommer]

It's a very common task. In most cases it's useful to create a master-calendar for all the usual period-fields but also to create various flag-fields and/or some pre-calculations/matchings there. That you don't have a real date isn't a problem because you could create one, for example with:

date(date#(Year&Month, 'FormatPattern'))

which would be the simplest approach but also various mappings or pick()  and then makedate() and similar are possible.

Many more information about this could you find here:

How-to-use-Master-Calendar-and-Date-Values 

- Marcus

[Josef1]

Many thanks for the answer.

I Know that it would be the best to create master calendar. For now i'm working in an evironment that reqires me to upload a lot of data manually and we don't have a solution yet to update data automatically, so there are changes happeneing to the data all the time since we are building everything on the server.

For now i would just like to know how to create such a syntax directly in the chart.

thanks

[marcus_sommer]

I don't want to say that's not possible with your strings but why doing it in such a complicated way? The creation of a master-calendar isn't very difficult at least not with the usual standard-fields and even the classical flags for YTD, LYTD and so on aren't really sophisticated.

The essential point is that a real date is a number and only with numbers you could compare them if they are < = > as another value. In your case if month and year would be numeric you might use expressions like:

sum({< Year =  {"$(=year(today()))"}, Month = {"<=$(=month(today()))"}>} Value)

sum({< Year =  {"$(=year(today())-1)"}, Month = {"<=$(=month(today()))"}>} Value)

- Marcus

[Josef1]

Because the data for 2019 is already in text, i'm uploading the 2020 data. that 2020 data needs to be in the same foramt in order to concatinate it. Every time i want to update the data with the latest month in qliq, i have to delete the 2020 data and upoload the new file of over 5 gig. i dont really have access to the server so i have to send this huge data file to a developer and he would upload the data to the server. It is not ideal but this what i have to work with now until we have a complete solution. it is temporary.

so have i understood it correct that it is not possible to create such a chart that i need with dates in text format?

[Josef1]

Hi Marcus

I have now created a master calender, but your syntax don't work as bellow.

sum({< Year =  {"$(=year(today()))"}, Month = {"<=$(=month(today()))"}>} Value)

/

sum({< Year =  {"$(=year(today())-1)"}, Month = {"<=$(=month(today()))"}>} Value)

-

1