Set Analysis with variable and other element

[Monkeyb33f]

Hi Everyone,

I'm not totally new to Qlik - but I haven't used it in a few years. I have inherited a Qlik Sense dashboard with a sophisticated data model and been asked to make some changes. The data model uses a canonical date table and a date bridge to allow the use of a variable - which is sensible and make sense to me. However, where I am struggling is to make a set expression use the variable AND another filter on an element. For example, in the date bridge table i have data as follows:

Key	DateType	Date	COUNTER
1	Created	2020-01-25	1
2	Created	2020-01-25	1
3	Created	2020-01-25	1
4	Approved	2020-01-25	1
5	Closed	2020-01-25	1

What i'm struggling with is to get the count of rows where the date is selected as a variable, but also only count those of a specific DateType. I tried count({<[DateType]=('Created'),$(vDate)>}[COUNTER]), but this seems to be giving me spurious results. Can anyone help with where i am going wrong?

[Vegar]

I see an syntax error in you DateType modifier. It should be [DateType]={'Created'}

Another error source could be the $(vDate), but it is hard to know as you are not sharing the content of your vDate variable.

[Monkeyb33f]

Thank you for getting back to me so quickly.

Forgive the typo on the syntax - that was just me transposing it.

The variable is as follows (this I have inherited and not written myself):

=

//Yesterday

if([Calendar] = 'Yesterday', (vYesterday),

//Current Week

if([Calendar] = 'Current Week' , (vCWeek),

//Current Month

if([Calendar] = 'Current Month' , (vCMonth),

//Current YTD

if([Calendar] = 'YTD' , (vYTDCal),

//Last 12 Months

if([Calendar] = 'Last 12 Months' , (vL12Months),

if(GetSelectedCount(Calendar) <='0',(vSelections),)                                                                                                                                                                        

)))))

[Taoufiq_Zarra]

from @Vegar 's post :

maye be :

=count({<[DateType]={'Created'},Date={'$(=vDate)'}>} [COUNTER])

vDate must be on 'YYYY-MM-DD' format, you can use

vDate =Date#(............,'YYYY-MM-DD')

for example if the load script :

LOAD Date#(Date) as Date,COUNTER,DateType,Key INLINE [
    Key, DateType, Date, COUNTER
    1, Created, 2020-01-25, 1
    2, Created, 2020-01-25, 1
    3, Created, 2020-01-26, 1
    4, Approved, 2020-01-25, 1
    5, Closed, 2020-01-25, 1
];

output :

[Monkeyb33f]

Hi,

Thanks for your input - the suggested change didn't help, but now that I look, the date format in the bridge table is actually DD/MM/YYYY format. As far as i can see that format has been used throughout all of the tables.

Can i change the variable to account for that, or does it need to be changed in the load script?

Thanks.

[Taoufiq_Zarra]

if I understood correctly

Date#(Date,''YYYY-MM-DD'') as Date

[Monkeyb33f · Accepted solution]

Just to close this off - the underlying data model was actually broken and the issue wasn't with the expression at all.