Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello qlik-community,
i want to set up a very simple star-scheme with qlik sense.
given the following query, how would you setup the script ?
btw. is there any chance to give the user an possibility to enter a date, which will be used by the query (USER_INPUT) ?
thanks in advance!
SELECT
SUM(
CASE WHEN dim.type in ('a','b','c') and dim.state in ('0','1') and dim.date <= USER_INPUT
THEN fact.revenue
CASE WHEN dim.type in ('d','e','f') and dim.state in ('0','1') and dim.date <= USER_INPUT
and dim.flag ='N'
THEN fact.revenue*(-1)
ELSE 0
END
),
dim.company_name
FROM DIMENSION dim, FACTS fact
WHERE dim.ID = fact=ID
hi clark,
i have attached the attachment. i it is qlikview app but qliksense app work same way.
use this app for reference. as u mentioned. i done calculation in load script. just after every new input u need to have to reload app. for refreshed data.
hope this will solve ur problem,
and for extension visit following link
GitHub - erikwett/qsVariable: Variable extension for Qlik Sense
Looks like you are modeling facts revenue about a company
There are lots of ways you could do this and it depends what you are lookng to achieve.
Start by seperating your data into dimensions and facts.
In the fact table you might have
%CompanyKey
Type ='Revenue'
Date
Value
adding a fact type will allow you to grown your model as you bring in more facts about your company.
In the Company table you might have
%CompanyKey
Company Name
Company Type
Company State
State, Type and flag im unsure if these are related to the company or the revenue you might discover other dimensions you need to report on as you investigate further.
For the user manipulation let Qlik create a calendar on your revenue date and the users can fliter to their hearts content on months, quarters, years etc... manipulating the date load is the wrong place for users to get invlovd unless you have a data size/business constraint that means you can only report x number of years of revenue.
If you need to control the user interaction more then your getting into variables and extensions but problably not need yet.
Hope this helps.
Andy
Hello Andy,
Thanks for your quick response.
My query reflects an already implemented data model.
One major table holds the facts and is connected via ID to the dimension table.
Some measures need a special treatment, which is done via several CASE-WHEN constructs,
where attributes from the dimension table have to be used.
I really don't want to put the logic into the application - it may confuse user and the maintenance is too difficult.
So, what are your recommended steps to proceed ?
Thanks in advance!
hi,
best way to achieve is to use input box extension which u can find in community. use that extension in that declare variable which will have the value we insert use that variable in ur expression.
And for the data model there is multiple ways to achieve just separate fact table and dimension table. create key between them which will give unique solution use master calendar for trends.
hope this will help
hello!
thanks for your hint.
in your solution a user has to define the measure every time by himself, which is very dangerous.
Therefore I thought to put the calculation into the load-script.
But if you know, how to get a kind of semantic-layer, which can be used for several apps, that would be great.
i could not find the extension in the community, can you provide a link ?
there are already separate tables available - and i linked them via ID.
don't know where to start...
hi clark,
i have attached the attachment. i it is qlikview app but qliksense app work same way.
use this app for reference. as u mentioned. i done calculation in load script. just after every new input u need to have to reload app. for refreshed data.
hope this will solve ur problem,
and for extension visit following link
GitHub - erikwett/qsVariable: Variable extension for Qlik Sense
Thx mayuresh dabhekar - i think i got the idea.