Divide measure before date X

[skoppe]

Hello all,

I really need some help, I have the following chart:

formulas are:

Aantal klantvragen: count({<[Klant label]={'XX'},[Month]=>}AppID)

Verzekerd: count({<[Klant label]={'XX'},[IsVerzekerde]={1},[Month]=>}AppID)

What I want and can't get working. I would like to divide al numbers before Date 25-11-2016 by 2. And round up.

numbers would be like:

Aantal klantvragen:

jan 238

feb 192

mrt 314

dec 230

I have a Date field in format 'DD-MM-YYYY'.

I hope someone can help me.

Thanks!

Regards Stefan

[Anonymous]

Hi Stefan,

Have you tried;

if(Date < '25-11-2016', count({<[Klant label]={'XX'},[Month]=>}AppID) /2, count({<[Klant label]={'XX'},[Month]=>}AppID))

[sunny_talwar]

May be like this?

If(Month(MakeDate(2016, 11, 25)) < Maand, Count({<[Klant label]={'XX'},[Month]=>}AppID)/2, Count({<[Klant label]={'XX'},[Month]=>}AppID))

[skoppe]

Thanks,

I've changed this to:

If(MakeDate(2016, 11, 25) < Date, Count({<[Klant label]={'XX'},[Month]=>}AppID)/2, Count({<[Klant label]={'XX'},[Month]=>}AppID))

Now it works when I select one date, when I select more dates I get the old numbers.

Any ideas?

Thanks, Stefan

[sunny_talwar]

Still trying to understand how this is being used.... Can you share images of how it looks when you select a single date and how it looks when you select multiple dates? May be a sample might help speed up things

[skoppe]

Hi Sunny,

Sample is not easy because of non disclosure agreement, scrambling screws up my set analysis.

I will try it with more images:

1) No selection on date gives bad result:

2) selection on date 1-1-2016 gives good result (15/2 = 7.5) :

3) selection on 2-1-2016 gives good result (16/2 = 8):

4) selection on 1-1-2016 and 2-1-2016 gives bad result:

So it only works when there is one date selected. The goal is that everything up to 25-11-2016 is divided by two (even without selections).

Regards,

Stefan

[sunny_talwar · Accepted solution]

Try may be using Aggr() function around this

Sum(Aggr(If(MakeDate(2016, 11, 25) < Date, Count({<[Klant label]={'XX'},[Month]=>}AppID)/2, Count({<[Klant label]={'XX'},[Month]=>}AppID)), Date, Maand))

But since you are doing this by date and not month, you will have more than half of the month of december where the result is divided by 2. Is that what you want?

[skoppe]

You mean more than half of the month november?

[skoppe]

This is it, great! Thanks!

[sunny_talwar]

Right, that's what I meant