2 Replies Latest reply on Oct 31, 2018 11:58 PM by Ankit Bansal

# Creating calculated field using the LOD Expression to derive groups

I have created a LOD expression for calculating the latest data from the data set shown below:

Latest Device Count:

if ({ INCLUDE [Device Name],[Store Id] : MAX([date])} = { INCLUDE [Device Name],[Store Id],[date]:MAX([date])}) THEN

[Devices Count] ELSE

0 END

The outcome I am achieving is shown below:

Query:

So from using the above calculation, I have to create a category Group of Device Count.

Like Device Count Category should have values like:

Desired Result:

So I want to calculate no. of stores as per the device count category:

Jonathan Drummey

Mahfooj Khan

Regards,

Faizi

• ###### 1. Re: Creating calculated field using the LOD Expression to derive groups

Hi,

I've modified your Latest Device Count calculated field

IF [Date] = {FIXED [Store Id],[Device Name]:MAX([Date])} THEN

[Device Count] ELSE

0 END

Create another field using below calculation to get the Store Device Count

{FIXED [Store Id]:SUM([Latest Device Count])}

Once done then create one more calculated field called Group

IF [Store Device count]<=1 THEN '1'

ELSEIF [Store Device count]<=2 THEN '2'

ELSEIF [Store Device count]<=3 THEN '3'

ELSEIF [Store Device count]<=4 THEN '4'

ELSE '5+'

END

Once done then you can drag the fields in canvas and check

workbook attached for your reference. Let us know if this help.

I've also mentioned same result creating bin on store device calculated field

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Creating calculated field using the LOD Expression to derive groups

Here you go:

Attached is the workbook.

Thanks,

AB

1 of 1 people found this helpful