topic Re: Dollar expansion not working in App Development

Dollar expansion not working

L_VN — Fri, 27 Sep 2024 10:47:14 GMT

I am trying to calculate a measure where I count the numbers if ID that have been open for at least 50 days in any given period.

I have a facts table where each ID has a FactsType = 'Open'. This is linked to the Calendar for all days from Start to Close - 1. This means that I want to calculate the number of distinct ID which are linked to the calendar on the Max(Date), who have a FactsType = 'Open' and where Max(Date) - From > 50.

I have a variable for Max(Date) called vMaxDate.

My expression looks like:

Count(DISTINCT Aggr( If("=$(vMaxDate)" - From > 50, ID), ID ) {<FactsType = {'Open'}, Date = {"=$(vMaxDate)"}>} )

But the dollar expansions do not work for whatever reason.

Could someone help me?

Re: Dollar expansion not working

Kushal_Chawda — Fri, 27 Sep 2024 10:56:55 GMT

@L_VN try below

Count(DISTINCT {<FactsType = {'Open'},ID = {"=$(vMaxDate)-From >50"}>} ID) or Count(DISTINCT {<FactsType = {'Open'}>}aggr(if($(vMaxDate)-From >50,ID),ID))

Re: Dollar expansion not working

p_verkooijen — Fri, 27 Sep 2024 11:11:17 GMT

@L_VN

How is the variable vMaxDate defined?

Does it contain a result, for instance: =DATE(Today(), 'M/D/YYYY') then the output = 9/27/2024
or only the base formula without starting with = the output would be : DATE(Today(), 'M/D/YYYY')

Based on the result of the variable the first usage of the variable could not need an dollar sign expansion. When you use $(vMaxDate) on the result 9/27/2024 it will calculate 1,6469038208169

Also the use of "" for the IF is incorrect, you only need it in the Set Analysis

Count(DISTINCT
Aggr(
If($(vMaxDate) - From > 50, ID),
ID
)
{<FactsType = {'Open'}, Date = {"=$(vMaxDate)"}>}
)

Besides that the complete expression seems incorrect.

This should work

Count({<FactsType = {'Open'}, From= {">=$(vMaxDate)-50"}, Date = {"$(vMaxDate)"}>} DISTINCT ID)

Please provide an output sample / formatting of

vMaxDate
From
Date

Re: Dollar expansion not working

L_VN — Mon, 30 Sep 2024 07:34:21 GMT

Oh, I never knew that the equals sign made a difference.

All three are floored ints, so it should work. I don't think this works. Qlik seems to interpret it as the string value (see my attached image).

From= {">=$(vMaxDate)-50"}

Edit: I moved the parenthesis to include the calculation

Re: Dollar expansion not working

marcus_sommer — Tue, 01 Oct 2024 15:25:09 GMT

I would probably do:

From= {">=$(=vMaxDate-50)"}