Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello.
I have three KPIS like this:
sum({ Type={'1'}>} number)
sum({ Type={'2'}>} number)
sum({ Type={'3'}>} number)
I have two issues:
1)When I pick a Type from the filter pane "Type" the two others don't equal to 0
2) To these 3 kpis i have second kpis that follow this formula , to have max dates vs same date last year comparison:
sum( {<[date]={ '$(= Date(max([date] ),'DD/MM/YYYY'))' },[Type]={'1'} >} number)
-sum( { <[date]={'$(=Date(AddYears(Max([date]), -1),'DD/MM/YYYY'))' } ,[Type]={'1'} > } number )
Now when i pick a Type in the filter pane they all change dynamically... shouldn't this work happen for different DATE picks???
Please help!
Specifying a field in set analysis overrides any selections made in a filter. You need to use an intersection. For Item 1, use:
sum({ Type*={'1'}>} number)
sum({ Type*={'2'}>} number)
sum({ Type*={'3'}>} number)
For Item 2, I'm not sure I completely understand what issue you are having.
Does this solve it?
sum( {<[date]={ '$(= Date(max([date] ),'DD/MM/YYYY'))' },[Type]*={'1'} >} number)
-sum( { <[date]={'$(=Date(AddYears(Max([date]), -1),'DD/MM/YYYY'))' } ,[Type]*={'1'} > } number )
Hi @ioannagr
Try like below
sum( {<[date]={ '$(= Date(max({<Type={1}>}[date] ),'DD/MM/YYYY'))' },[Type]={'1'} >} number)
-sum( { <[date]={'$(=Date(AddYears(Max({<Type={1}>}[date]), -1),'DD/MM/YYYY'))' } ,[Type]={'1'} > } number )
And
sum({<Type={1}>} number)
Hello, you both gave me solutions, @GaryGiles for my 1st question and @MayilVahanan for the 2nd.
Thank you so much.
(although @GaryGiles , i don't understand why this works like this. for item1. For example when in set analyis when i specify type=2 but in the filter i pick type 1, why does set analysis change when i refer to type 2 and not 1 😕 ? )
Where you specify sum({ Type={'2'}>} number), Qlik will ignore any selection made in the type field. That is just how set analysis works. It is actually very useful to have that behavior in some scenarios. The reason that sum({ Type*={'2'}>} number) works with selections is that it performs a set intersection between the value you specify and the available value(s) for the field. When you make selections in the filter, the available values are the ones you select. So, if nothing is selected, the intersection returns 2. If a selection is made, but 2 is not included, the intersection will return no values.
Hope that helps.
@GaryGiles yes, very much! Thanks 🙂