Summery of monthly data with daily condition

[Ishragil]

Hi

I have the following monthly data:

and I would like to present one line which summerise only the date where the number of compiled-lines is greater than 100.

Is it possible to do it in the layout or is it neccessery to do it in the script? 

If it is possible to do it in the layout I would like to learn how.

Thanks

[Dalton_Ruer]

You mean you want all of the dates on a single row like "1/3/2021, 7/3/2021, 8/3/2021 ..." or do you also want the compiled lines and hours "1/3/2021 - 106 - 12.73, 7/3/2021 - 678 - 10.73" 

Not sure how readable that is a for a person viewing it. Why does it need to be on 1 line? 

[Ishragil]

Thanks for your reply

What I mean is to take the data by date, such as:

And to summeries the monthly total of the marked lines only (Where the compiler picked more than 100 lines) by compiler, to reach the following:

[Dalton_Ruer]

Step 1. When Loading your data do the conversion to add the field for Month-Year like "Month(Date)&'-'&Year(Date)"

Step 2. Set the Limitation condition on your compiler field

Step 3: Have a Snickers and a Mountain Dew

[Ishragil]

Just steped back to this issue.

I guess I wasn't clear enough with defining the problem.

My problem is not to summaries the lines by Month, but to summaries only the lines that are greater than 100.

For example:

When this is the daily data:

I want to summaries by Month only the yellow marked lines (where compiled lines >100) to get the following result:

compiled lines: 438+375=813 (excluding the 79 lines from 10/3/21 and the 84 lines from 12/3/21)

The expressions I need in free language are:

Count(compiled lines) if (Count(compiled lines by compiler per date)>100)

and

Sum(Hours) if (Count(compiled lines by compiler per date)>100)

[stevejoyce]

Sum({<[Compiled lines] = {">100"} >}  [Compiled lines])

[Ishragil]

If I do it for the all month the condition will always take place and will summaries all the lines including the days when less than 100 lines where compiled

I need to summeries the compiled lines only for the days where compiled lines>100

[stevejoyce]

I don't completely follow... but you have your Month dimension in the table.  

Sum({<[Compiled lines] = {">100"} >}  [Compiled lines]) will only sum records in the data model that are > 100.

if you need to first aggregate by date, and only include dates where the sum > 100, this should work...

Sum({<Date = {"=Sum([Compiled lines]) >100"} >}  [Compiled lines])

[Ishragil]

You Wrote:

if you need to first aggregate by date, and only include dates where the sum > 100, this should work...

Sum({<Date = {"=Sum([Compiled lines]) >100"} >}  [Compiled lines])

Thanks for your answer.

It seems to work, so If I may ask for something more: What if I need the same for two criterias?

Where this condition is valid by date and compiler, meanning:

Summaries only lines where each compiler in each day compiled over 100 lines

[stevejoyce · Accepted solution]

Here are a few scenarios that may help you as needed going forward...

1)If you want to put 2 conditions on a single field (in this case Date) you can do like:

Sum({<Date = {"=Sum([Compiled lines]) >100 and expression2 > xxx"} >}  [Compiled lines])

2) 2 different fields could be done like:

Sum({<Date = {"=Sum([Compiled lines]) >100"}, field2 = {"=condition"} >}  [Compiled lines])

3) 2 different sets with combinations of fields in each:

Sum({

<Date = {"=Sum([Compiled lines]) >100"}, field2 = {"=condition"} >

*

<Date = {"=Sum([Compiled lines]) >200"}, field3 = {"=condition"} >}  [Compiled lines])