Expression that assumes a selection is made

[Anonymous]

Hi

I have an expression below

Previous month:  Count({<[Calendar Year/Month] ={'$(vYMLess1)'}, Month= >}     Distinct [Business Partner Parent ID]))

Current  month:  Count({<[Calendar Year/Month] ={'$(vYM)'}, Month= >}     Distinct [Business Partner Parent ID]))

where vYMless1 relates to the previous month and vYM is the current month

so for eg , if  vYM = 201706  , then vYMLess1 = 201705

and Month will be June and May respectively.

The issue I'm having is with the formula relating to the previous month. It only works if I select June and May.  If i only select June, then the previous months formula gives an incorrect answer.

So basically, what i need is to create an expression for the previous month that assumes that a selection is made for the current month and previous month.

Please can you assist.  I hope I have conveyed my message clearly.

Kind regards

Nayan

[Anil_Babu_Samineni]

If you are selecting Month Field, Could be the reason you are not selecting Year. Can you try selection happen from [Calendar Year/Month] field and then check whether it comes or not. If not, I would suggest you to share sample application to work

[kaushiknsolanki]

Hi,

Can you tell us what expression you have written in both variables.

Regards,

Kaushik Solanki

[Anonymous]

Hi

If Year is selected, there is no change.

Actually the formula given above is a simplistic information. My actual formula is as follows which is an aggregation.

Unfortunately i cannot share the model because of sensitivity of information. 

Kind regards

Nayan

sum(aggr(

    ( Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

        [Sales Org] = {'RU01'},

        [Target Distribution Channel] = {'31','32'},

        [Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

        [Terrotory Cluster] -= {'05'},

        Counter = {'1'},

        [Trade Channel Name] = {'Traditional Trade'},

        SegmentationMapped={'Gold','Silver', 'Gold+'},

        [Territory Cluster] -= {'Autonom. Distr'},

        Country = {'Russia'},

        Month

        >}

        Distinct [Business Partner Parent ID])

+

Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

        [Sales Org] -= {'RU01'},

        [Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

        [Terrotory Cluster] -= {'05'},

        Counter = {'1'},

        [Trade Channel Name] = {'Traditional Trade'},

        SegmentationMapped={'Gold','Silver', 'Gold+'},

          [Territory Cluster] -= {'Autonom. Distr'},

          Country = {'Russia'},

          Month

        >}

        Distinct [Business Partner Parent ID]))

        , Outlet))

[Anonymous]

Hi Kaushik

As requested.

vYM

=max([Calendar Year/Month])

vYMLess1  =Year(Date(AddMonths(Makedate(left(Trim($(vYM)),4),Right(Trim($(vYM)),2),'01'),-1)))&Num(Month(Date(AddMonths(Makedate(left(Trim($(vYM)),4),Right(Trim($(vYM)),2),'01'),-1))),'00')

Kind regards

Nayan

[kaushiknsolanki]

Hi,

First of all check of the vYMLess1 is taking any value when you dont select any month?

If yes then you need to check the expression, else you should focus on variable.

I would write it in other way like below.

vYMLess1

=Date(Addmonths(Date(Date#($(vYM),'YYYYMM')),-1,'YYYYMM')

Regards,

Kaushik Solanki

[Anonymous]

Hi Kaushik

If i don't select any month, the vYMLess1 is showing correct values.  Thank you for your reply.

Not sure if you seen my expression when i replied to Anil.

Kind regards

Nayan

[kaushiknsolanki]

I can see that you have missed the '=' for the Month Field.

Your expression should be.

sum(aggr(

    ( Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

        [Sales Org] = {'RU01'},

        [Target Distribution Channel] = {'31','32'},

        [Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

        [Terrotory Cluster] -= {'05'},

        Counter = {'1'},

        [Trade Channel Name] = {'Traditional Trade'},

        SegmentationMapped={'Gold','Silver', 'Gold+'},

        [Territory Cluster] -= {'Autonom. Distr'},

        Country = {'Russia'},

        Month=

        >}

        Distinct [Business Partner Parent ID])

+

Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

        [Sales Org] -= {'RU01'},

        [Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

        [Terrotory Cluster] -= {'05'},

        Counter = {'1'},

        [Trade Channel Name] = {'Traditional Trade'},

        SegmentationMapped={'Gold','Silver', 'Gold+'},

          [Territory Cluster] -= {'Autonom. Distr'},

          Country = {'Russia'},

          Month=

        >}

        Distinct [Business Partner Parent ID]))

        , Outlet))

Regards,

Kaushik Solanki

[Anonymous]

Hi Kaushik

I have added in the "=" sign, however no difference.  Does it make a difference to put an "=".

Kind regards

Nayan

[kaushiknsolanki]

Hi,

I feel it should work.

Only way to look more into is to see actual data.

Please share the sample application.

Regards,

Kaushik Solanki