topic Re: Exclude future values on line-chart with accumulation on the measure in App Development

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

krzys — Mon, 20 Dec 2021 21:02:46 GMT

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?

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

Ksrinivasan — Tue, 02 Mar 2021 08:51:32 GMT

hi,

you can try this logic to avoid future value of 117,

if(Date_week<= Week(Today), Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}ID]) ,'')

ksrini.

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

krzys — Tue, 02 Mar 2021 09:03:08 GMT

Thanks for the tip @Ksrinivasan

I did try that before, and it works well if there is no accumulation. The chart shows nothing instead of zeros.

But once I turn on the accumulation to see the growth, the result is the same.

Your idea will work if I have a way to precalculate accumulated values in a separate table... Or somehow exluce Max vales?

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

Ksrinivasan — Tue, 02 Mar 2021 09:08:42 GMT

hi,

thank for your reply, and good to hear,

you can use same logic for

if(weekday<= week(today),Cummulative ,Cumulative *0)

ksrini.

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

krzys — Tue, 02 Mar 2021 09:16:20 GMT

hey again @Ksrinivasan

The result for accumulation is the same but without accumulating is worse as zeros appeared again

I used this

if([Date.autoCalendar.Week]<= Week(Today),Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]) ,Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]) *0)

In principle it is correct that accumulation is giving these values. It takes MAX until data is reported and they just add zeros until the end of axis X.

Unless you had something else in mind?

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

Ksrinivasan — Tue, 02 Mar 2021 10:12:24 GMT

hi,

ok, try it,

if([Date.autoCalendar.Week]<= Week(Today),Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]) ,(Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID])-Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]))

ksrini

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

krzys — Tue, 02 Mar 2021 10:47:08 GMT

Hey, unfortunately, it is not it. I get incorrect values for both: accumulated and regular values

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

Ksrinivasan — Tue, 02 Mar 2021 10:50:41 GMT

hi,

can you share your qvf.

ksrini

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

krzys — Tue, 02 Mar 2021 11:01:41 GMT

sure @Ksrinivasan

QVF and data attached

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

krzys — Tue, 02 Mar 2021 17:01:28 GMT

hey @Ksrinivasan

It seems it not possible...(?) - thanks for trying

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

Ksrinivasan — Tue, 02 Mar 2021 17:58:29 GMT

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

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

krzys — Tue, 02 Mar 2021 18:03:51 GMT

Thank you @Ksrinivasan

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

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

Ksrinivasan — Tue, 02 Mar 2021 18:21:38 GMT

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

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

krzys — Wed, 03 Mar 2021 09:47:29 GMT

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

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

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

Ksrinivasan — Thu, 04 Mar 2021 04:08:54 GMT

hi,

what you have selected,

which field set as filter

ksrini

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

krzys — Thu, 04 Mar 2021 14:01:36 GMT

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.