Exclude future values on line-chart with accumulation on the measure

[krzys]

Dear Community,

I came across an issue I feel I am close to solving, but actually, this might not be possible to solve.

I want to have two lines on a line chart showing the accumulated growth per week for my measures. Ideally, I would like the two lines to be as close to each other. This will mean that we are good on the track.

Sample data is 2 columns (attached)

In the chart underneath, I use:

Measure 2020:  Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2020'}>}ID]) - modification on Accumulation full

Measure 2021:  Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}ID]) - modification on Accumulation full

Dimension:  [Date.autoCalendar.Week]

The above chart is close to perfection, but I would like to achieve the same but without 117 for all the future weeks. I would rather this line be visible (without accumulating, these would be just zeros, but I would not see the growth) unless you have a way to show forecast instead of 117 for future weeks. That would be even better.

Do you have any ideas?

[Ksrinivasan]

hi,

Use this script:

SSASA:
LOAD
ID,
Date("Date",'DD-MM-YYYY') as Date,
Year(Date) as Year,
1 as DD
FROM [lib://REPORT EXTRACTION /SSSS1.xlsx]
(ooxml, embedded labels, table is Sheet27);
Join
Load
'sssss' as ID,
Sum(If(Year(Date) = 2021, DD))*-1 as DD,
Date(WeekStart(Today())+8,'DD-MM-YYYY') as Date,
Year(Date(WeekStart(Today())+8,'DD-MM-YYYY')) as Year
resident SSASA;

result:

Dimension: Week(Date)

Maesure for 2020: rangesum(above(sum({$<Year={"2020"}>}DD),0,rowno()))

Measure for 2021: rangesum(above(sum({$<Year={"2021"}>}DD),0,rowno()))

Chart like below

ksrini

[krzys]

Thank you @Ksrinivasan 

I cannot wait to try this out tomorrow. Will be there a way to hide zeros too? 🙂

[Ksrinivasan]

hi,

yes,

hi,

Use this script:

SSASA:
LOAD
ID,
Date("Date",'DD-MM-YYYY') as Date,
Year(Date) as Year,
1 as DD
FROM [lib://REPORT EXTRACTION /SSSS1.xlsx]
(ooxml, embedded labels, table is Sheet27);
Join
Load
'sssss' as ID,
Sum(If(Year(Date) = 2021, DD))*-1.1 as DD,
Date(WeekStart(Today())+8,'DD-MM-YYYY') as Date,
Year(Date(WeekStart(Today())+8,'DD-MM-YYYY')) as Year
resident SSASA;

result:

Dimension: Week(Date)

Maesure for 2020: rangesum(above(sum({$<Year={"2020"}>}DD),0,rowno()))

Measure for 2021: rangesum(above(sum({$<Year={"2021"}>}DD),0,rowno()))

Chart like below:

in chart Y axis min value should '0'

ksrini

[krzys]

Hey @Ksrinivasan 

Thanks for the formulas. Things seem to work nicely (TAHNK YOU) but the problem comes up again if I do any selection. Do you know what might be causing this?

I modified the script like that to exclude zeros totally

SSASA:

LOAD

ID

Date([Date],'DD-MM-YYYY') as Date,

Year([Date]) as Year,

1 as DD;

Join

Load

'sssss' as ID,

Sum(If(Year(Date) = 2021, DD))*-1 as DD,

Date(WeekStart(Today())+8,'DD-MM-YYYY') as Date,

Year(Date(WeekStart(Today())+8,'DD-MM-YYYY')) as Year

resident SSASA;

measure: if( rangesum(above(sum({$<Year={"2021"}>}DD),0,rowno()))>0, rangesum(above(sum({$<Year={"2021"}>}DD),0,rowno())),)

WITHOUT SELECTIONS

WITH SELECTION

[Ksrinivasan]

hi,

what you have selected,

which field set as filter

ksrini

[krzys]

hey,

I reproduced the logic in my main app and it shows everything as we expected but when I select any other dimensions it goes the same again showing the same amount across future weeks.