topic Re: Including Dimensions in Set expression in QlikView

Including Dimensions in Set expression

Thu, 03 Nov 2016 13:40:00 GMT

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.

Re: Including Dimensions in Set expression

Anonymous — Thu, 03 Nov 2016 14:40:44 GMT

Hello,

I'm not sure if I have understand. Please can you post a test .qlw file?

Thank you.

Re: Including Dimensions in Set expression

rubenmarin — Thu, 03 Nov 2016 15:09:23 GMT

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).

Re: Including Dimensions in Set expression

Thu, 03 Nov 2016 15:18:31 GMT

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 )

Re: Including Dimensions in Set expression

Thu, 03 Nov 2016 15:29:11 GMT

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.

Re: Including Dimensions in Set expression

Thu, 03 Nov 2016 19:42:27 GMT

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

Re: Including Dimensions in Set expression

Fri, 04 Nov 2016 22:44:51 GMT

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.

Re: Including Dimensions in Set expression

jayanttibhe — Sat, 05 Nov 2016 15:40:20 GMT

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.

Re: Including Dimensions in Set expression

Sun, 06 Nov 2016 17:53:10 GMT

Can someone help me out here please?

Thanks

Re: Including Dimensions in Set expression

rubenmarin — Mon, 07 Nov 2016 09:03:34 GMT

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.

Re: Including Dimensions in Set expression

Mon, 07 Nov 2016 12:11:46 GMT

Thanks for your reply Ruben.My bad, let me upload the one with data. But I am using the number format for dates,

=Sum({<DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}SLS_RPT_TRNS.COST_USD_AMT)

My requirement is to restrict the Sum(SLS_RPT_TRNS.COST_USD_AMT) to the dates SITE_DT but it is being shown for the entire period now.

For Australia, the value should be 12533601 which is the value from the beginning of Oct2016 till 8th oct, but it being shown as 307899808 which is the value for the entire month of Oct.