Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am looking for Set expression which will consider the last column to calculate the Nr column. In the example below the value
Aus-Oct2016 should be 8. It is being shown as 14 here due to the entries in one of the Fact tables. the SITE_DT column is from a different Fact table and i want the Nr to calculate the days from the Site_DT.
Please let me know if more information is needed.
Nr:
Max({<DT_CONS_DIM.FISC_YR_NBR={"$(=Only({1}YearofToday))"},
DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}DT_CONS_DIM.FISC_DAY_OF_MTH_NBR)
Period | CTRY_NAM | Dr | Nr | Date(Max(INV_RPT_TRNS.SITE_DT)) |
Oct-16 | Australia | 28 | 14 | 10/9/2016 |
Oct-16 | Canada | 28 | 8 | 10/9/2016 |
Oct-16 | Germany | 28 | 15 | 10/16/2016 |
Oct-16 | IN | 28 | 15 | 10/16/2016 |
Oct-16 | India | 28 | 14 | |
Oct-16 | Mexico | 28 | 15 | 10/16/2016 |
Oct-16 | Netherlands | 28 | 8 | 10/9/2016 |
Oct-16 | UN | 28 | 8 | 10/9/2016 |
Oct-16 | US | 28 | 15 | 10/16/2016 |
Oct-16 | 28 | |||
Sep-16 | Australia | 35 | 35 | 9/25/2016 |
Sep-16 | Canada | 35 | 29 | 9/25/2016 |
Sep-16 | Germany | 35 | 34 | 9/25/2016 |
Sep-16 | IN | 35 | 29 | 9/25/2016 |
Sep-16 | India | 35 | 35 | |
Sep-16 | Mexico | 35 | 34 | 9/25/2016 |
Sep-16 | Netherlands | 35 | 29 | 9/25/2016 |
Sep-16 | UN | 35 | 29 | 9/25/2016 |
Sep-16 | US | 35 | 35 | 9/25/2016 |
Sep-16 | 35 | |||
Aug-16 | Canada | 28 | 22 | 8/21/2016 |
Aug-16 | Germany | 28 | 22 | 8/21/2016 |
Aug-16 | IN | 28 | 22 | 8/21/2016 |
Aug-16 | Mexico | 28 | 22 | 8/21/2016 |
Aug-16 | UN | 28 | 22 | 8/21/2016 |
Aug-16 | US | 28 | 28 | 8/21/2016 |
Aug-16 | 28 |
Thanks.
Hello,
I'm not sure if I have understand. Please can you post a test .qlw file?
Thank you.
Hi Ganesh, I don't understand the required operation either.
For your comments, at least for Oct-2016, seems you want "Day(Date(Max(INV_RPT_TRNS.SITE_DT)))-1", but I don't know the operations for other Periods (or the '14' for India in Oct-2016).
In any case note that the $-expansion is calculated for the whole table, not row by row, so "$(=Num(Max(INV_RPT_TRNS.SITE_DT)))" will return the Max of all the selected data (same value for all rows).
Hello,
I will not be able to post a sample file here, however i have solved hafl the problem, by including the P(INV_RPT_TRNS.SITE_DT) in my Set expression.
Max({<DT_CONS_DIM.FISC_YR_NBR={"$(=Only({1}YearofToday))"},
DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"},INV_RPT_TRNS.SITE_DT=p(INV_RPT_TRNS.SITE_DT)>}DT_CONS_DIM.FISC_DAY_OF_MTH_NBR)
Now the remaining half of my problem is to calculate the Sum of one of the measures using the below expression, but it is returning zero.
=Sum({<FISC_YR_NBR={"$(=Only({1}YearofToday))"},INV_RPT_TRNS.SITE_DT=p(INV_RPT_TRNS.SITE_DT),DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}SLS_RPT_TRNS.COST_USD_AMT )
Hello Ruben,
It is not simple as "Day(Date(Max(INV_RPT_TRNS.SITE_DT)))-1" , it is actually showing the days from a different fact table where there entries, but i want to restrict them to the SITE_DT mentioned here.
And this SITE_DT is from another Fact table.
In simple terms, I want to Sum a particular measure from the first Fact table till the date from the second fact table. But when i use the Max() as you have mentioned, it is not doing it by rows. Any suggestions to overcome this?
Thanks.
Hello,
I used the below expression to get the right numbers, now the challenge is to calculate the Sum of a particular measure using this function in Set expression.
This works as expected:
FirstSortedValue(FISC_DAY_OF_MTH_NBR,-Aggr(FISC_DAY_OF_MTH_NBR, INV_RPT_TRNS.SITE_DT,CTRY_NAM))
This does not work as expected, showing only zeroes:
=Sum({<FISC_DAY_OF_MTH_NBR={"<=$(=FirstSortedValue(FISC_DAY_OF_MTH_NBR,-Aggr(FISC_DAY_OF_MTH_NBR, INV_RPT_TRNS.SITE_DT,CTRY_NAM)))"}>}SLS_RPT_TRNS.COST_USD_AMT)
Oct-16 | Australia | 28 | 7 | 0 | 10/8/2016 |
Oct-16 | Austria | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | Canada | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | China | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | Germany | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | Hong Kong | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | IN | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | India | 28 | 0 | ||
Oct-16 | Mexico | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | Netherlands | 28 | 7 | 0 | 10/8/2016 |
Oct-16 | Switzerland | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | Thailand | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | UN | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | US | 28 | 14 | 0 | 10/15/2016 |
Oct-16 | 28 | 0 | |||
Sep-16 | Australia | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | Austria | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | Canada | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | China | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | Germany | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | Hong Kong | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | IN | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | India | 35 | 0 | ||
Sep-16 | Mexico | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | Netherlands | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | Switzerland | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | Thailand | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | UN | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | US | 35 | 35 | 0 | 10/1/2016 |
Sep-16 | 35 | 0 | |||
Aug-16 | Australia | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Austria | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Canada | 28 | 14 | 0 | 8/13/2016 |
Aug-16 | China | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Germany | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Hong Kong | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | IN | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Mexico | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Netherlands | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Switzerland | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | Thailand | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | UN | 28 | 14 | 0 | 8/13/2016 |
Aug-16 | US | 28 | 28 | 0 | 8/27/2016 |
Aug-16 | 28 | 0 |
Attaching the sample application here, please give me your suggestions.
Intention is to calculate the Fourth column based on the dates in the last column or the Nr column which is the day number of that month.
I dont know whats the problem in set expression and not exactly getting what you want to do. But even if you
put simply =Sum(SLS_RPT_TRNS.COST_USD_AMT)
it shows all Zeros... that means there are no Non zero Measures against the Dimensions in the Data model.
Can someone help me out here please?
Thanks
Hi Ganesh, as Jayant saids, there is nothing to sum, not only with the current dimension but in all the data. If you check the [document properties]->Tables you can see there is no records for the SLS_RPT_TRNS table.
Maybe it's because the where condition, it's filtering to retrieve only data for this month and i'm not sure if that comparison is working, the date format of the document it's different that the one used in the where condition.
Date format of the document: M/D/YYYY
Date format used in condition: YYYY-MM-DD
To avoid issues with dates format usually it's better use the numeric value that represent the date.