Set analysis on date

[viveksingh]

Hi experts,

I Have a requirement ti show data for a department on a day.

For exampl, let say I have data like below

DATE	DEPT	NAME	SAL
20/7/2018	FIN	KL	250
30/8/2018	HR	DEF	80
1/9/2018	IT	ABC	100
2/9/2018	IT	HG	180

‌

if we select dept as IT we get ABC nd HG data. But if we select dept IT nd date 1/9/2018, we get only one record as we have one record.

currently I ‘m showing data on daily basis. So i have stored my date in a variable.

Let v_date=Date(Today()-10)

and using the variable in set analysis.

Now i want to show data for IT dept and use above variable in set analysi In such a way that even if there is no record for IT dept, it should show as zero.

[tush]

Hi,

     you can do it it by using Pivot table.

for more understanding please refer below link:-

Show all values in Pivot/Straight Table (irrespective of current selection)

[viveksingh]

‌thanks for thevTushar.

the above post is helpful, but works only when selected. In my senario, there were no selections. I’m including my date variable in the set analysis only.

[tresesco · Accepted solution]

Are you looking for something like:

[viveksingh]

‌yes. perfectly. Thanks Tresesco.

could you please help me in understanding avg(1)

[tresesco]

In a simpler word : this aggregated function without the set analysis helps keep the dimension values intact even if they don't have data (filtered by set analysis). Hope this helps.

[viveksingh]

‌Thank you very much