5 Replies Latest reply on Sep 26, 2016 2:08 AM by Sreekanth Kasaraneni

# Two Compare two Different counts bases on Previous months and Current months user filter selection

 Dimensions - YEAR and MONTH fields MEASURE - AR COUNT condition : Show the UP , DOWN and Equal to Indicators based on the Logic below Logic - Comparing the AR count For the current & previos months depending on User Filters User Filter is Date Silder ( user Selects the date ) example : T1: start date 07/01/2016 End date 09/30/2016 Bring count for this Timeperiod T1 T2 :GO back to 3months Prev start date 04/01/2016 Previous End date 06/30/2016 Bring count for this Time perios T2 Main calculation : To compare count for T1 and T2 Periods and show the indicators based on the count if T1 > T2 then high indicator in T1

Attaching Excel sheet with Logic and DATA

• ###### 1. Re: Two Compare two Different counts bases on Previous months and Current months user filter selection

Hi Suvarshika,

please find the attached workbook and let me know if you need any changes.

i have created following fields with the data given in the attached workbook.

Month_Year

[Month]+"/"+[Year]

Custom_Date

DATE(DATEPARSE("MMM/yyyy",[Month_Year]))

Created four Parameters Curr Start date, Curr End date, Pre Start date, Pre End date with 3 months difference

T1

IF [Custom_Date] >= [Curr start date] AND [Custom_Date] <= [Curr End date] THEN [Count] END

T2

IF [Custom_Date] >= [ Pre start date] AND [Custom_Date] <= [Pre End date] THEN [Count] END

KPI

IF SUM([T1]) > SUM([T2]) THEN "High Indicator"

ELSEIF SUM([T1]) < SUM([T2]) THEN "Low Indicator"

ELSEIF SUM([T1]) = SUM([T2]) THEN  "Equal Indicator"

END

• ###### 2. Re: Two Compare two Different counts bases on Previous months and Current months user filter selection

Thanks Sreekanth..!

In the Dashboard i can't show 2 parameter sliders - Start date and end date slider filters

I should show only single slider in the dashboard

Is there a way to Concatenate two date parameters into Single Date or away to Create this Logic with date function without using parameters

I tried start date - Min(date) , End date - Max(date )

calculated the date difference

Prev start date - Min(date)-date Diff

Prev end date - MIn(date)-1

while trying to create KPI logic getting Error msg - Aggregate and non aggregate function cannot be mixed

• ###### 3. Re: Two Compare two Different counts bases on Previous months and Current months user filter selection

Currently I have used only one parameter and created your requirement

We need to use parameter for getting 3 months data or comparison we cannot use filter for comparison as separate columns

Thanks,

Sreekanth

1 of 1 people found this helpful
• ###### 4. Re: Two Compare two Different counts bases on Previous months and Current months user filter selection

Thankyou

But I have a requirement to show single date slider with start date and end date where user can select "n" Number of months

Example :

if user selects 5months he should  see indicators calculation based on last 5 months and current 5months

• ###### 5. Re: Two Compare two Different counts bases on Previous months and Current months user filter selection

Hi Suvarshika,

Try to validate the changes in the attached workbook sheet3.

i think you requirement is as shown in the below figure. if you put the date in the filter shelf and select range of dates option, you will be getting the #Count# values only between the dates selected in the filter, can't get the previous months data. ( Try to explain to your end user)

so to my knowledge  you should definitely use a parameter date and another parameter for No. of months to change dynamically.

below are the changes made in the attached workbook.

Added new parameter (No Of Months)  range from 1 to 100.

T1 (Current Duration count)

IF [Custom_Date] >= [Choose date] AND [Custom_Date] <= DATEADD('month',(INT([No Of Months])-1),[Choose date]) THEN [Count] END

T2 (Previous Duration Count)

IF [Custom_Date] >= DATEADD('month',-INT([No Of Months]),[Choose date])

AND [Custom_Date] <= DATEADD('month',-1,[Choose date]) THEN [Count] END