Monthly bar chart

[mohan2391]

I have 2 date columns

Date1, Date2, Sales

Jan24, Jan24, 100

Jan24, Feb24, 110

Feb24, Feb24, 200

Feb24, Mar24, 210

Mar24, Mar24, 300

Mar24, Apr24, 310

I have to show a bar chart with

DIMENSION = Date1

MEASURE = Sum(Sales) for Date2 as next month

I mean, Jan24 bar should show 110, Feb24 bar should show 210 and Mar24 bar should show 310.

How to write set expression.

[lennart_mo]

Not a set expression but should work just as well if both Date1 and Date2 are formatted as proper Dates

Sum(if(Date2 = AddMonths(Date1,1), Sales))

[mohan2391 · Accepted solution]

Thank You Lennart_mo

somehow this doesn’t work for me, but I have got below solution from other question, and it worked.

————————

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)