Dates/Months Missing

[veeranj]

Hi All ,

I have data in the below format .

ID	date(dd-mm-yyyy)	type
id1	01-01-2017	A
id2	01-02-2017	A
id3	01-03-2017	B
id4	01-04-2017	B

Now i want to display the count of IDs for the month of March and April for Type='A'

and Count of IDs  for the month of Jan and Feb for Type='B' .

i have master calendar generated linked to this date.

When i am filtering type=A in set analysis,the other dates are missing .

So how to get those Months .I would like to display data monthwise .

Can someone help on this .

Thanks,

Veera

[sunny_talwar · Accepted solution]

May be like this

Count({<type = {A}>}ID) + Avg({1} 0)

Make sure to uncheck 'Suppress Zero Values' on the presentation tab of the chart properties

[veeranj]

Hi Sunny,

Great.That worked. Thanks you so much for the easy solution .

i did in this way as below .

Table:

load *,Date&Type as Existskey;

Load ID,Date,Type from table ;

concatenate(Table)

load * where not exists(Existskey,Date&Type ) ; 

load Date,'A' as Type resident mastercalendar ;

Above resident will laod all dates where Type=A is not present .

same i did for Type=B.

Will there be any performance lag if we do it this way at script level .

I tried using sum(0) instead of Avg(0) .that also worked.

[sunny_talwar]

Depends on what you want... if you are getting the result without script manipulation, then why do it? But if you feel more comfortable that you have all data points for all types (and there are not too many of them), then you can go ahead with it.