Monthly Bar chart - with next month version

[mohan2391]

Hi Team,

Please find the screenshot.

Measure1 is same for a particular month no matter what version is it. (For example, Measure1 is always 10 for May-24)

Measure2 is different for a particular month in different versions (For example, Measure2 is 100 for May-24 in version 202406, but it is 110 in version 202405)

As per business request, we have to show how Measure2 is changing over in current version and last 2 versions. So, we are concatenating the data of diff versions. But, while concatenating Measure1 is also getting concatenated. Because of this, in UI, in charts, when we write Sum(Measure1) it is showing double.

For example, for May-24 Measure1 is showing 20 instead of 10.

If we eliminate Measure1 while concatenating, then we are missing that month's value as am writing "Where Not Exists()". 

For this, I thought of 1 suggestion.

in UI, in the bar chart, I have dimension as Period, measure as Sum(Measure1). I am trying to put Sum({<Version={"=$(Date(Addmonths(Period,1),'YYYYMM'))"}>}Measure1)

but it is working only when I select 1 month, but I want it to be working fine for all months in the bar chart without any month selection.

Please help.

[mohan2391]

Any help would be appreciated.

[DGO]

If Measure1 is always the same and you don't want to double it using the sum function, how about Max(Measure1)?

[mohan2391 · Accepted solution]

Hi DGO,

Thanks for your suggestion. This solution is working fine in some cases but failing in some cases when I include in my app with real data.

Anyways, I have got the solution from other question and it is working fine for me. Thanks again.

————————————-

tmpData:

LOAD * Inline [

Date1, Date2, Sales

01/01/2024, 01/01/2024, 100
01/01/2024, 01/02/2024, 110
01/02/2024, 01/02/2024, 200
01/02/2024, 01/03/2024, 210
01/03/2024, 01/03/2024, 300
01/03/2024, 01/04/2024, 310

];

Data:
NoConcatenate Load *, If(Date2> Date1,1,0) As DateCheck Resident tmpData; Drop Table tmpData;

Then expression is : sum({<DateCheck = {'1'}>}Sales)