Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
Are you looking for something like:
yes. perfectly. Thanks Tresesco.
could you please help me in understanding avg(1)
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.
Thank you very much